如何在PostgreSQL中创建一个返回动态列名的查询？

Question

我在一个报告数据库中有两个表，一个是订单表，另一个是订单项表。每个订单可以有多个订单项，每个订单项都有对应的数量：

Orders
+----------+---------+
| order_id | email   |
+----------+---------+
| 1        | [e-mail] |
+----------+---------+
| 2        | [e-mail] |
+----------+---------+
| 3        | [e-mail] |
+----------+---------+

Order Items
+---------------+----------+----------+--------------+
| order_item_id | order_id | quantity | product_name |
+---------------+----------+----------+--------------+
| 1             | 1        | 1        | Tee Shirt    |
+---------------+----------+----------+--------------+
| 2             | 1        | 3        | Jeans        |
+---------------+----------+----------+--------------+
| 3             | 1        | 1        | Hat          |
+---------------+----------+----------+--------------+
| 4             | 2        | 2        | Tee Shirt    |
+---------------+----------+----------+--------------+
| 5             | 3        | 3        | Tee Shirt    |
+---------------+----------+----------+--------------+
| 6             | 3        | 1        | Jeans        |
+---------------+----------+----------+--------------+

为了报告目的，我希望将这些数据反规范化到一个单独的PostgreSQL视图中（或仅运行一个查询），将上面的数据转换成以下形式：

+----------+---------+-----------+-------+-----+
| order_id | email   | Tee Shirt | Jeans | Hat |
+----------+---------+-----------+-------+-----+
| 1        | [e-mail] | 1         | 3     | 1   |
+----------+---------+-----------+-------+-----+
| 2        | [e-mail] | 2         | 0     | 0   |
+----------+---------+-----------+-------+-----+
| 3        | [e-mail] | 3         | 1     | 0   |
+----------+---------+-----------+-------+-----+

ie，它是订单中每个项目数量的总和，以产品名称为列标题。我是否需要使用类似于crosstab的东西来完成这个任务，或者是否有一种聪明的方法可以使用子查询，即使在查询运行之前我不知道不同产品名称的列表。

- Joe

---

如果我不知道独特产品名称的列表，请使用交叉表。 - Vao Tsun

@Joe：你能解决问题吗？ - lakshman

很不幸，我必须在查询中列出每个产品，这并不是一个特别好的查询。仍在寻找使用交叉表的好方法。 - Joe

Answer 1

这是一个可能的答案：

create table orders 
(
  orders_id int PRIMARY KEY, 
  email text NOT NULL
);

create table orders_items 
(
  order_item_id int PRIMARY KEY, 
  orders_id int REFERENCES orders(orders_id) NOT NULL, 
  quantity int NOT NULL, 
  product_name text NOT NULL
);

insert into orders VALUES (1, '[e-mail]');
insert into orders VALUES (2, '[e-mail]');
insert into orders VALUES (3, '[e-mail]');

insert into orders_items VALUES (1,1,1,'T-Shirt');
insert into orders_items VALUES (2,1,3,'Jeans');
insert into orders_items VALUES (3,1,1,'Hat');
insert into orders_items VALUES (4,2,2,'T-Shirt');
insert into orders_items VALUES (5,3,3,'T-Shirt');
insert into orders_items VALUES (6,3,1,'Jeans');


select 
  orders.orders_id, 
  email,
  COALESCE(tshirt.quantity, 0) as "T-Shirts",
  COALESCE(jeans.quantity,0) as "Jeans",
  COALESCE(hat.quantity, 0) as "Hats"
from 
  orders 
  left join (select orders_id, quantity from orders_items where product_name = 'T-Shirt') 
    as tshirt ON (tshirt.orders_id = orders.orders_id)
  left join (select orders_id, quantity from orders_items where product_name = 'Jeans') 
    as jeans ON (jeans.orders_id = orders.orders_id)
  left join (select orders_id, quantity from orders_items where product_name = 'Hat') 
    as hat ON (hat.orders_id = orders.orders_id)
;

已使用PostgreSQL进行测试。结果：

 orders_id |  email  | T-Shirts | Jeans | Hats 
-----------+---------+----------+-------+------
         1 | [e-mail] |        1 |     3 |    1
         2 | [e-mail] |        2 |     0 |    0
         3 | [e-mail] |        3 |     1 |    0
(3 rows)

根据您的评论，您可以尝试像这样使用tablefunc：

CREATE EXTENSION tablefunc;

SELECT * FROM crosstab
(
  'SELECT orders_id, product_name, quantity FROM orders_items ORDER BY 1',
  'SELECT DISTINCT product_name FROM orders_items ORDER BY 1'
)
AS
(
       orders_id text,
       TShirt text,
       Jeans text,
       Hat text
);

但我认为您对SQL的想法是错误的。通常您知道需要哪些行并且必须告诉SQL。"旋转表格"90度不是SQL的一部分，应该避免使用。

- Janning Vygen

---

这很好，而且肯定有效。但我真正需要的是像这样的查询，它可以扩展到约30个产品名称，其中产品名称是通过像select distinct(product_name) from order_items这样的查询检索出来的。 - Joe