Sql 按所选的值将记录分组到不同的列中

发布于06月21日

我举了以下例子:

create table products (name text, id integer, type smallint, category integer);

insert into products values 
('apple', 23, 1, 1200),
('apple', 23, 1, 999),
('apple', 31, 2, 1200),
('apple', 23, 3, 1200),
('orange', 10, 1, 200),
('orange', 10, 2, 200),
('orange', 10, 2, 230),
('orange', 10, 3, 200),
('orange', 64, 1, 700),
('orange', 70, 2, 700);

DB FIDDLE个

我希望结果是这样的:

name	type_1_3_id	type_2_id
apple	23	31
orange	{10,64}	{10,70}

但是Postgres强制我按列类型进行分组，这使得我的结果不是我想要的结果

select distinct name, 
case when type in (1,3) then array_agg(distinct id) end as type_1_3,
case when type = 2 then array_agg(distinct id) end as type_2
from products
group by 1, type
order by 1

推荐答案

在array_agg函数中使用filter子句:

select name, 
 array_agg(distinct id) filter (where type in (1,3)) as type_1_3,
 array_agg(distinct id) filter (where type = 2) as type_2
from products
group by 1
order by 1

对于条件聚合array_agg(case when... then ... end)，不匹配的行将返回空值，而过滤器将仅聚合WHERE子句中的指定行.