时间戳

首页 > 解决方案 > 将 postgresql 查询结果从行转置为按列值分组

sql - 将 postgresql 查询结果从行转置为按列值分组

问题描述

产品表

INSERT INTO public."Products" ("ID", "Name", "Description") VALUES (1, 'Shirt A', 'Nice to wear');
INSERT INTO public."Products" ("ID", "Name", "Description") VALUES (2, 'Shirt B', 'Looks good');
INSERT INTO public."Products" ("ID", "Name", "Description") VALUES (3, 'Shirt C', 'Light fabric');
INSERT INTO public."Products" ("ID", "Name", "Description") VALUES (4, 'Shirt D', 'Waterproof');

属性表

INSERT INTO public."Attributes" ("ProductID", "Type", "Value") VALUES (1, 'Color', 'Green');
INSERT INTO public."Attributes" ("ProductID", "Type", "Value") VALUES (1, 'Color', 'Red');
INSERT INTO public."Attributes" ("ProductID", "Type", "Value") VALUES (1, 'Size', 'M');
INSERT INTO public."Attributes" ("ProductID", "Type", "Value") VALUES (1, 'Size', 'L');
INSERT INTO public."Attributes" ("ProductID", "Type", "Value") VALUES (3, 'Color', 'Green');
INSERT INTO public."Attributes" ("ProductID", "Type", "Value") VALUES (4, 'Size', 'L');
INSERT INTO public."Attributes" ("ProductID", "Type", "Value") VALUES (4, 'Size', 'XL');

SQL Query组合了以下输出中需要的结果

ID, NAME,   Description,   Color,        Size
1, Shirt A, Nice to wear,  [Green, Red], [M, L]
2, Shirt B, Looks good,    null,         null
3, Shirt C, Light fabric,  [Green],      null
4, Shirt D, Waterproof,    null,         [L, XL]

标签： sqlpostgresqltranspose

解决方案

这是一个join有条件的聚合：

select p.*,
       array_agg(a.value) filter (where type = 'Color') as colors,
       array_agg(a.value) filter (where type = 'Size') as sizes
from products p left join
     attributes a
     on p.id = a.productid
group by p.id;

请注意，Postgres 允许您在按主键聚合时选择表的所有列。我假设这id是 的主键products。

另外，在定义表格时不要使用双引号。他们只是混乱查询。

推荐阅读