database – 如何在Postgresql中创建返回动态列名的查询?
发布时间:2020-12-13 16:07:33 所属栏目:百科 来源:网络整理
导读:我在报告数据库中有两个表,一个用于订单,一个用于订单项.每个订单可以包含多个订单商品,以及每个订单商品的数量: Orders+----------+---------+| order_id | email |+----------+---------+| 1 | 1@1.com |+----------+---------+| 2 | 2@2.com |+---------
我在报告数据库中有两个表,一个用于订单,一个用于订单项.每个订单可以包含多个订单商品,以及每个订单商品的数量:
Orders +----------+---------+ | order_id | email | +----------+---------+ | 1 | 1@1.com | +----------+---------+ | 2 | 2@2.com | +----------+---------+ | 3 | 3@3.com | +----------+---------+ 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 | 1@1.com | 1 | 3 | 1 | +----------+---------+-----------+-------+-----+ | 2 | 2@2.com | 2 | 0 | 0 | +----------+---------+-----------+-------+-----+ | 3 | 3@3.com | 3 | 1 | 0 | +----------+---------+-----------+-------+-----+ 即,它是订单中每个商品的数量与产品名称的总和;并将产品名称设置为列标题.我是否需要使用交叉表这样的东西,或者使用子查询是否有一种聪明的方法,即使在查询运行之前我不知道不同产品名称的列表. 解决方法
这是一个可能的答案:
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,'1@1.com'); insert into orders VALUES (2,'2@2.com'); insert into orders VALUES (3,'3@3.com'); insert into orders_items VALUES (1,1,'T-Shirt'); insert into orders_items VALUES (2,3,'Jeans'); insert into orders_items VALUES (3,'Hat'); insert into orders_items VALUES (4,2,'T-Shirt'); insert into orders_items VALUES (5,'T-Shirt'); insert into orders_items VALUES (6,'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 | 1@1.com | 1 | 3 | 1 2 | 2@2.com | 2 | 0 | 0 3 | 3@3.com | 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的一部分,应该避免. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |