在SQL Server中查找含NULL值的重复记录并返回唯一键

嘿，这个问题太常见了——SQL里的NULL确实是个让人头疼的点，因为标准SQL里NULL = NULL的结果既不是TRUE也不是FALSE，而是UNKNOWN，所以你的原查询里没法匹配到那些包含NULL的重复行。我给你几个不同的解决方案，你可以根据自己的SQL Server版本和业务情况选择：

核心思路是把所有可能为NULL的列，在分组和连接时替换成一个不会和真实业务数据冲突的占位值，让NULL被当成相同的值处理。比如字符串列可以用'__NULL_MARKER__'，数值列用-999999（选一个你的数据里绝对不会出现的值）。

修改后的查询代码：

with MyJoinedTable as (
    select 
        MyKey, -- 注意你原代码里写的是PolicyNumber，应该对应Table1的MyKey吧？
        Col1, Col2, Col3, ..., Col49
    from Table1 
    inner join Table2 on Table1.table2ID = Table2.ID
)
select 
    t1.MyKey, t1.Col1, t1.Col2, ..., t1.Col49
from MyJoinedTable t1
inner join (
    select 
        ISNULL(Col1, '__NULL_MARKER__') as Col1, 
        ISNULL(Col2, '__NULL_MARKER__') as Col2,
        -- 对每一列都做同样的处理，数值列替换成专属占位值
        ...,
        ISNULL(Col49, '__NULL_MARKER__') as Col49
    from MyJoinedTable 
    group by 
        ISNULL(Col1, '__NULL_MARKER__'), 
        ISNULL(Col2, '__NULL_MARKER__'),
        ...,
        ISNULL(Col49, '__NULL_MARKER__')
    having count(*) > 1
) t2 on 
    ISNULL(t1.Col1, '__NULL_MARKER__') = ISNULL(t2.Col1, '__NULL_MARKER__') 
    and ISNULL(t1.Col2, '__NULL_MARKER__') = ISNULL(t2.Col2, '__NULL_MARKER__')
    ...
    and ISNULL(t1.Col49, '__NULL_MARKER__') = ISNULL(t2.Col49, '__NULL_MARKER__')
order by t1.Col1, t1.Col2

注意：占位值一定要选对，比如如果你的字符串列允许空字符串，就不能用''当占位符，否则会把空字符串和NULL误判成相同值。

这种方法更简洁，通过生成每行所有数据列的哈希值，只要哈希值相同，就说明所有列内容一致（包括NULL）。需要注意给列之间加分隔符，避免不同列的内容拼接后产生哈希碰撞。

with MyJoinedTable as (
    select 
        MyKey,
        Col1, Col2, ..., Col49,
        -- 生成所有数据列的哈希值，列顺序要和拼接顺序一致
        HASHBYTES('SHA2_256', 
            CONCAT(
                ISNULL(Col1, '__NULL__'), '|',
                ISNULL(Col2, '__NULL__'), '|',
                -- 每个列之间加分隔符，防止内容拼接冲突
                ...,
                ISNULL(Col49, '__NULL__')
            )
        ) as RowHash
    from Table1 
    inner join Table2 on Table1.table2ID = Table2.ID
)
select 
    t1.MyKey, t1.Col1, t1.Col2, ..., t1.Col49
from MyJoinedTable t1
inner join (
    select RowHash
    from MyJoinedTable
    group by RowHash
    having count(*) > 1
) t2 on t1.RowHash = t2.RowHash
order by t1.Col1, t1.Col2

如果你用的是SQL Server 2022及以上版本，官方提供了直接处理NULL相等判断的特性：NULLS NOT DISTINCT和IS NOT DISTINCT FROM，可以完美解决你的问题，代码也最简洁。

with MyJoinedTable as (
    select 
        MyKey,
        Col1, Col2, ..., Col49
    from Table1 
    inner join Table2 on Table1.table2ID = Table2.ID
)
select 
    t1.MyKey, t1.Col1, t1.Col2, ..., t1.Col49
from MyJoinedTable t1
inner join (
    select Col1, Col2, ..., Col49
    from MyJoinedTable
    group by Col1, Col2, ..., Col49
    with (NULLS NOT DISTINCT) -- 关键：让NULL被视为相同值分组
    having count(*) > 1
) t2 on 
    t1.Col1 IS NOT DISTINCT FROM t2.Col1
    and t1.Col2 IS NOT DISTINCT FROM t2.Col2
    ...
    and t1.Col49 IS NOT DISTINCT FROM t2.Col49
order by t1.Col1, t1.Col2

IS NOT DISTINCT FROM会正确处理NULL：NULL IS NOT DISTINCT FROM NULL返回TRUE，完全符合我们对重复的定义。

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