使用CROSSTAB实现表的行列翻转,数据的统计
表1: products
id name
1 Synergistic Marble Gloves
2 Gorgeous Paper Table
3 Awesome Wool Gloves
4 Ergonomic Leather Clock
5 Heavy Duty Iron Knife
6 Heavy Duty Paper Shirt
7 Fantastic Aluminum Table
8 Rustic Paper Plate
9 Rustic Leather Keyboard
10 Intelligent Leather Clock
表2:details
id product_id detail
1 10 bad
2 1 bad
3 9 ok
4 4 good
5 2 good
6 5 bad
7 7 bad
8 1 ok
9 8 good
10 8 ok
11 10 ok
12 6 bad
13 7 good
14 8 ok
15 6 bad
16 2 bad
17 9 bad
18 7 good
19 9 ok
sql:
SELECT name, good, ok, bad
FROM crosstab('
SELECT p.name, d.detail, COUNT(1)
FROM details AS d JOIN products AS p
ON d.product_id = p.id
GROUP BY p.name, d.detail
ORDER BY p.name, d.detail
')
AS ct(name text, bad bigint, good bigint, ok bigint);
结果:
name good ok bad
Aerodynamic Concrete Hat 39 32 31
Awesome Leather Pants 31 31 29
Durable Granite Computer 25 35 37
Durable Linen Knife 45 37 29
Enormous Wooden Plate 27 38 43
Incredible Bronze Bench 37 29 47
Mediocre Copper Keyboard 25 37 26
Mediocre Granite Bag 33 35 36
Rustic Steel Watch 24 44 26
Small Granite Lamp 25 36 31