如何用SQL筛选拥有完全相同无重复爱好的成员对？

现有数据表 #

members表结构及数据：

需求 #

筛选出仅拥有完全相同无重复爱好的成员对，期望输出：

原SQL及问题 #

当前使用的SQL语句：

SELECT m1.id as id1 , m2.id as id2
FROM members m1 inner join members m2
ON m1.id < m2.id
WHERE m1.hobby in (
  SELECT distinct(m2.hobby)
  )
GROUP BY id1,id2

实际输出错误包含了(2,4)，原因是原SQL仅保证m1的爱好存在于m2的爱好集合中，但未验证两者爱好集合完全匹配（包括爱好数量、所有爱好双向存在），也未处理原表中的重复爱好。

修正后的SQL方案 #

方案一：用聚合字符串匹配（简洁直观） #

先对每个用户的爱好去重并排序聚合，确保相同爱好集合生成一致的字符串，再进行匹配：

WITH user_unique_hobbies AS (
    SELECT 
        id,
        -- 去重后按爱好排序拼接，保证相同集合的字符串一致
        STRING_AGG(DISTINCT hobby, ',' ORDER BY hobby) AS hobby_set,
        COUNT(DISTINCT hobby) AS hobby_count
    FROM members
    GROUP BY id
)
SELECT 
    uh1.id AS id1,
    uh2.id AS id2
FROM user_unique_hobbies uh1
JOIN user_unique_hobbies uh2 
    ON uh1.id < uh2.id
    AND uh1.hobby_set = uh2.hobby_set
    AND uh1.hobby_count = uh2.hobby_count;

方案二：用计数验证集合等价性 #

通过分组计数验证两个用户的爱好集合完全双向包含且数量一致：

SELECT 
    uh1.id AS id1,
    uh2.id AS id2
FROM (
    -- 先对原表去重，得到每个用户的唯一爱好列表
    SELECT DISTINCT id, hobby FROM members
) uh1
JOIN (
    SELECT DISTINCT id, hobby FROM members
) uh2 
    ON uh1.id < uh2.id
GROUP BY uh1.id, uh2.id
HAVING 
    -- 两者的唯一爱好数量相同
    COUNT(DISTINCT uh1.hobby) = COUNT(DISTINCT uh2.hobby)
    -- 所有uh1的爱好都存在于uh2中（匹配数量等于uh1的爱好总数）
    AND COUNT(DISTINCT uh1.hobby) = COUNT(DISTINCT CASE WHEN uh2.hobby = uh1.hobby THEN uh1.hobby END);

说明 #

两种方案都先处理了原表中的重复爱好，再验证成员对的爱好集合是否完全一致：

• 方案一通过字符串聚合简化了集合比较，适合大多数支持STRING_AGG的数据库（如PostgreSQL、SQL Server 2017+）
• 方案二用纯计数逻辑，兼容性更强，适合不支持字符串聚合的数据库

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