CROSSTAB 的使用

使用CROSSTAB实现表的行列翻转，数据的统计

表1： products

id  name1   Synergistic Marble Gloves2   Gorgeous Paper Table3   Awesome Wool Gloves4   Ergonomic Leather Clock5   Heavy Duty Iron Knife6   Heavy Duty Paper Shirt7   Fantastic Aluminum Table8   Rustic Paper Plate9   Rustic Leather Keyboard10  Intelligent Leather Clock

表2：details

id  product_id  detail1   10  bad2   1   bad3   9   ok4   4   good5   2   good6   5   bad7   7   bad8   1   ok9   8   good10  8   ok11  10  ok12  6   bad13  7   good14  8   ok15  6   bad16  2   bad17  9   bad18  7   good19  9   ok

sql:

SELECT name, good, ok, badFROM 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      badAerodynamic Concrete Hat                 39     32      31Awesome Leather Pants                    31     31      29Durable Granite Computer                 25     35      37Durable Linen Knife                      45     37      29Enormous Wooden Plate                    27     38      43Incredible Bronze Bench                  37     29      47Mediocre Copper Keyboard                 25     37      26Mediocre Granite Bag                     33     35      36Rustic Steel Watch                       24     44      26Small Granite Lamp                       25     36      31

https://www.postgresql.org/docs/9.5/static/tablefunc.html