postgres在几张桌子上聚集在一起

Question

我有以下表格：

targeting
-- id
-- type
-- value

performance
-- id
-- ad_name
-- installs

performance_targeting
-- id
-- performance_id
-- targeting_id

我想得到每一个表现，它的目标值。

为了实现这一目标，我写道：

select performance_id, string_agg(value, ',' order by value) as values from 
targeting join performance_targeting
on targeting.id = performance_targeting.id
group by performance_id

但结果并不正确。

参见木琴 --正如您所看到的，id 1的性能与目标年龄18-24岁和女性有关。我希望array_agg显示以下内容：

performance_id   targeting_values 
1                18-24,female
2                25-34,male
3                18-24,female
4                25-34,female

怎么啦？

Answer 1

你有个小虫子。你有performance_targeting.id而不是performance_targeting.targeting_id

select performance_id, 
   string_agg(value, ', ' ORDER BY value) as values 
from targeting 
join performance_targeting on targeting.id = performance_targeting.targeting_id
group by performance_id

演示