如何使用两个聚合函数(例如Double string_agg或只有sum)，但确保结果不包含由另一个聚合函数(由Second Join导致)引起的重复项？我使用的是PostgreSQL.
示例
我有三张桌子:
create table boxes
(
id bigserial primary key,
name varchar(255)
);
create table animals
(
id bigserial primary key,
name varchar(255),
age numeric,
box_id bigint constraint animals_boxes_id references boxes
);
create table vegetables
(
id bigserial primary key,
name varchar(255),
weight numeric,
box_id bigint constraint vegatables_box_id references boxes
);
一些输入数据:
insert into boxes (name) values ('First box');
insert into animals (box_id, name, age) values (1, 'Cat', 2);
insert into animals (box_id, name, age) values (1, 'Cat', 3);
insert into animals (box_id, name, age) values (1, 'Dog', 5);
insert into vegetables (box_id, name, weight) values (1, 'Tomato', 20);
insert into vegetables (box_id, name, weight) values (1, 'Cucumber', 30);
insert into vegetables (box_id, name, weight) values (1, 'Potato', 50);
我想把动物的名字放在盒子里:
select b.name as box_name,
string_agg(a.name, ', ' order by a.id) as animal_names
from boxes as b
left join animals a on b.id = a.box_id
group by b.name;
它是有效的:
| box_name | animal_names |
|---|---|
| First box | Cat, Cat, Dog |
但我也想知道蔬菜的名字.但它是doesn't work:
select b.name as box_name,
string_agg(a.name, ', ' order by a.id) as animal_names,
string_agg(v.name, ', ' order by v.id) as vegatable_names
from boxes as b
left join animals a on b.id = a.box_id
left join vegetables v on b.id = v.box_id
group by b.name;
它会产生动物名称和蔬菜名称的重复:
| box_name | animal_names | vegatable_names |
|---|---|---|
| First box | Cat, Cat, Cat, Cat, Cat, Cat, Dog, Dog, Dog | Tomato, Tomato, Tomato, Cucumber, Cucumber, Cucumber, Potato, Potato, Potato |
结果应该是:
| box_name | animal_names | vegatable_names |
|---|---|---|
| First box | Cat, Cat, Dog | Tomato, Cucumber, Potato |
我不能简单地添加distinct来删除重复项，因为:
- 表中的名称可以重复(名称为
Cat的两只动物).如果我使用distinct，它将生成Cat, Dog而不是Cat, Cat, Dog. - 我在
string_agg中用order by(distinct加起来就是ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list).即使我移走order by(string_agg(distinct a.name, ', '))，我也不能使用它，因为第一点.
更多信息
它适用于所有聚合函数:string_agg、array_agg、json_object_agg甚至sum.
动物年龄总和:
select sum(a.age)
from boxes as b
left join animals a on b.id = a.box_id
-- left join vegetables v on b.id = v.box_id
group by b.name;
在没有第二次联接的情况下，它计算正确(10)，但由于重复，计算错误(30).