SQL实现：从friendship表查询多用户共有的关联关系

现有存储好友关系的friendship表，包含userid_1、userid_2两个字段，样例数据如下：

需要编写SQL统计共同关联同一个userid_2的所有userid_1：将关联同一userid_2的userid_1聚合为逗号分隔的列表，最终输出两列：聚合后的userid_1列表、对应的共同关联用户common_userid，期望输出样例如下：

不同数据库的字符串聚合函数存在差异，对应SQL如下：

MySQL #

SELECT
  GROUP_CONCAT(userid_1 ORDER BY userid_1 SEPARATOR ', ') AS userid_1,
  userid_2 AS common_userid
FROM friendship
GROUP BY userid_2
-- 过滤仅被单个用户关联的记录，只保留有共同关联的结果
HAVING COUNT(*) >= 2;

PostgreSQL #

SELECT
  STRING_AGG(userid_1::text, ', ' ORDER BY userid_1) AS userid_1,
  userid_2 AS common_userid
FROM friendship
GROUP BY userid_2
HAVING COUNT(*) >= 2;

SQL Server #

SELECT
  STRING_AGG(userid_1, ', ') WITHIN GROUP (ORDER BY userid_1) AS userid_1,
  userid_2 AS common_userid
FROM friendship
GROUP BY userid_2
HAVING COUNT(*) >= 2;

• 按userid_2字段分组，对每组内的userid_1做字符串拼接聚合
• 聚合时增加排序规则，保证输出的用户ID列表顺序稳定，和预期格式对齐
• 通过HAVING子句过滤掉仅被单个用户关联的userid_2，只返回存在多个共同关联者的结果，和样例输出匹配

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