PostgreSQL内连接查询如何去除重复数据？

你的问题根源在于多表内连接产生了笛卡尔积，导致聚合时重复值被多次计入。解决方法很直接：给每个array_agg函数加上distinct关键字就能自动去重，同时要删掉原语句末尾的多余逗号，避免语法错误。

修改后的查询语句如下：

SELECT academy_projects.project_name,
array_agg(distinct academy_technology.name) as academy_technology,
array_agg(distinct academy_users.user_name) as academy_users,
array_agg(distinct academy_classes.name) as academy_classes
FROM academy_projects
inner join academy_technology  on academy_technology.id=any(academy_projects.technology_id)
inner join academy_users  on academy_users.user_id = any(academy_projects.user_id)
inner join academy_classes  on academy_classes.id = any(academy_projects.classes_id)  
group by academy_projects.project_name

修改完成后，academy_classes这类字段会返回去重后的结果，比如你期望的{insan,scooter}，不会再出现重复值。

内容的提问来源于stack exchange，提问作者Atakan Ertürk