SQL Server：如何查询单表两列中满足多对多(N:N)关系的记录

核心筛选逻辑为同时满足两个规则：

• 当前记录的Column1取值，在全表中关联至少2个不同的Column2取值
• 当前记录的Column2取值，在全表中关联至少2个不同的Column1取值

方案1：窗口函数实现（效率最优，适合大数据量场景） #

SELECT Column1, Column2
FROM (
    SELECT 
        Column1,
        Column2,
        -- 统计当前Column1对应的不同Column2数量
        COUNT(DISTINCT Column2) OVER (PARTITION BY Column1) AS col1_rel_cnt,
        -- 统计当前Column2对应的不同Column1数量
        COUNT(DISTINCT Column1) OVER (PARTITION BY Column2) AS col2_rel_cnt
    FROM TestManytoMany
) t
WHERE col1_rel_cnt >= 2 AND col2_rel_cnt >= 2

如果你的SQL Server版本不支持窗口函数中使用DISTINCT，可以使用下面的兼容方案。

方案2：子查询关联实现（兼容性最好，支持所有SQL Server版本） #

SELECT t.Column1, t.Column2
FROM TestManytoMany t
-- 关联筛选出满足多关联要求的Column1集合
INNER JOIN (
    SELECT Column1
    FROM TestManytoMany
    GROUP BY Column1
    HAVING COUNT(DISTINCT Column2) >= 2
) valid_c1 ON t.Column1 = valid_c1.Column1
-- 关联筛选出满足多关联要求的Column2集合
INNER JOIN (
    SELECT Column2
    FROM TestManytoMany
    GROUP BY Column2
    HAVING COUNT(DISTINCT Column1) >= 2
) valid_c2 ON t.Column2 = valid_c2.Column2

结果验证 #

用你提供的测试表执行上述代码，返回结果如下，完全匹配你需要的第8条记录：

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