Postgres 10如何将关联匹配的多行数据合并为单行展示

基础拼接查询 #

要实现同一外键关联的多行值合并为单行，使用PostgreSQL内置的字符串聚合函数string_agg即可，拼接时通过ORDER BY保证值的顺序一致，避免相同集合产生不同拼接结果：

SELECT 
  t1.name,
  string_agg(t2.name, '' ORDER BY t2.name) AS combined_values
FROM table1 t1
INNER JOIN table2 t2 ON t1.pk = t2.fk
GROUP BY t1.pk, t1.name;

执行后即可得到你需要的输出格式，示例中的查询结果为：

注：如果你需要和示例完全一致的拼接顺序，可以把ORDER BY t2.name替换为ORDER BY t2.pk，只要排序规则固定，就能保证相同关联值集合的拼接结果一致。如果要避免不同标签拼接产生歧义，也可以把拼接分隔符从空串替换为不会出现在标签中的字符，比如'|'，得到blue|green|yellow格式的结果。

重复关联值组查询 #

要找出存在完全相同关联值的table1记录，基于上面的拼接结果过滤重复的拼接值即可：

WITH combined_result AS (
  SELECT 
    t1.pk AS table1_pk,
    t1.name AS table1_name,
    string_agg(t2.name, '' ORDER BY t2.name) AS combined_values
  FROM table1 t1
  INNER JOIN table2 t2 ON t1.pk = t2.fk
  GROUP BY t1.pk, t1.name
)
SELECT *
FROM combined_result
WHERE combined_values IN (
  SELECT combined_values
  FROM combined_result
  GROUP BY combined_values
  HAVING COUNT(*) > 1
);

示例数据下该查询会返回fred和tom对应的两条记录，两者的关联值集合完全一致。

内容的提问来源于stack exchange，提问作者Paul Taylor