如何识别表中同一行内至少两列存在非空重复值的行？

先给你一个通用SQL方案，几乎能适配所有支持CTE和UNION ALL的数据库（比如MySQL、PostgreSQL、Oracle、SQL Server等），核心思路是把每行的非空列值拆成多行，统计每个ID下重复出现的值，再筛选出符合条件的行：

WITH value_counts AS (
    -- 把每个非空列的值拆成ID+值的行
    SELECT ID, COL1 AS col_value FROM your_table WHERE COL1 IS NOT NULL
    UNION ALL SELECT ID, COL2 FROM your_table WHERE COL2 IS NOT NULL
    UNION ALL SELECT ID, COL3 FROM your_table WHERE COL3 IS NOT NULL
    UNION ALL SELECT ID, COL4 FROM your_table WHERE COL4 IS NOT NULL
    -- 要是表中有更多列，继续按这个格式添加UNION ALL即可
), duplicate_ids AS (
    -- 找出同一个ID下，某个值出现至少2次的ID
    SELECT ID
    FROM value_counts
    GROUP BY ID, col_value
    HAVING COUNT(*) >= 2
)
-- 关联原表获取完整行数据
SELECT t.*
FROM your_table t
JOIN duplicate_ids d ON t.ID = d.ID;

如果你的数据库不支持CTE（比如老版本MySQL），可以改成子查询版本：

SELECT t.*
FROM your_table t
JOIN (
    SELECT ID
    FROM (
        SELECT ID, COL1 AS col_value FROM your_table WHERE COL1 IS NOT NULL
        UNION ALL SELECT ID, COL2 FROM your_table WHERE COL2 IS NOT NULL
        UNION ALL SELECT ID, COL3 FROM your_table WHERE COL3 IS NOT NULL
        UNION ALL SELECT ID, COL4 FROM your_table WHERE COL4 IS NOT NULL
    ) vals
    GROUP BY ID, col_value
    HAVING COUNT(*) >= 2
) d ON t.ID = d.ID;

针对Oracle的优化方案 #

Oracle有专门的集合函数，不用拆分行就能高效统计不同值的数量：

SELECT *
FROM your_table t
WHERE (
    -- 统计当前行的非空列总数
    CASE WHEN COL1 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN COL2 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN COL3 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN COL4 IS NOT NULL THEN 1 ELSE 0 END
) > (
    -- 统计当前行的非空值的去重数量
    CARDINALITY(
        SET(
            COLLECT(CASE WHEN COL1 IS NOT NULL THEN COL1 END) ||
            COLLECT(CASE WHEN COL2 IS NOT NULL THEN COL2 END) ||
            COLLECT(CASE WHEN COL3 IS NOT NULL THEN COL3 END) ||
            COLLECT(CASE WHEN COL4 IS NOT NULL THEN COL4 END)
        )
    )
);

原理很简单：如果非空列的总数大于去重后的非空值数量，说明至少有一个值在多个列中重复出现。

针对SQL Server的优化方案 #

SQL Server可以用字符串拼接+拆分的方式，更直观地统计重复值：

SELECT t.*
FROM your_table t
-- 统计当前行的非空值去重数量
CROSS APPLY (
    SELECT COUNT(DISTINCT value) AS distinct_count
    FROM STRING_SPLIT(
        CONCAT(
            CASE WHEN COL1 IS NOT NULL THEN CONCAT(',', COL1) ELSE '' END,
            CASE WHEN COL2 IS NOT NULL THEN CONCAT(',', COL2) ELSE '' END,
            CASE WHEN COL3 IS NOT NULL THEN CONCAT(',', COL3) ELSE '' END,
            CASE WHEN COL4 IS NOT NULL THEN CONCAT(',', COL4) ELSE '' END
        ), ','
    )
    WHERE value <> ''
) vals
-- 统计当前行的非空列总数
CROSS APPLY (
    SELECT COUNT(*) AS non_null_count
    FROM (VALUES (COL1), (COL2), (COL3), (COL4)) v(col)
    WHERE col IS NOT NULL
) cnts
-- 非空列数大于去重值数，说明有重复
WHERE cnts.non_null_count > vals.distinct_count;

用你的示例数据测试的话，这几个方案都会返回ID为1和3的行，完全符合需求。

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