SQL去重问题：如何保留contact为'no'的重复条目而非'yes'的

原始表 table1 数据： #

emp     checking    saving   cd     contact    type
tom     100.00      100.00  100.00  no          x         
tom     100.00      100.00  100.00  yes         NULL
bob     200.00      200.00  200.00  no          z         
bob     200.00      200.00  200.00  yes         NULL
mike    250.00      100.00  50.00   yes         NULL
alice   500.00      210.00  10.00   yes         NULL

期望结果： #

emp    checking   saving    cd  contact type
tom    100.00   100.00  100.00  no      x         
bob    200.00   200.00  200.00  no      z         
mike   250.00   100.00  50.00   yes     NULL
alice  500.00   210.00  10.00   yes     NULL

需求说明 #

• 同一emp存在除contact、type外其他字段完全一致的记录时，保留contact='no'的条目，移除contact='yes'的条目
• 若员工仅存在一条记录，直接保留

错误的查询语句 #

select x.*
from (select *,
             row_number() over (partition by  emp order by type ) as seqnum
      from table1
     ) x
where seqnum = 1

问题原因 #

你的排序逻辑不符合需求：

1. 仅按emp分区会把同一员工的所有记录（哪怕账户金额不同）都归为一组，可能误判非重复记录
2. 按type排序时，contact='yes'的记录中type为NULL，多数数据库默认NULL排序优先级高于非NULL值，导致这类记录被标记为seqnum=1，最终被保留，而你需要的contact='no'的记录反而被过滤掉

修正后的查询语句 #

select x.emp, x.checking, x.saving, x.cd, x.contact, x.type
from (
    select *,
           row_number() over (
               partition by emp, checking, saving, cd 
               order by case when contact = 'no' then 1 else 2 end
           ) as seqnum
    from table1
) x
where seqnum = 1

逻辑解释 #

1. 精准分区：partition by emp, checking, saving, cd确保只有当员工的账户金额完全一致时才视为重复记录，避免误判
2. 强制排序优先级：用case语句让contact='no'的记录排在最前面，确保这类记录被标记为seqnum=1并保留；重复的contact='yes'记录会被标记为seqnum=2，最终被过滤
3. 清理结果字段：外层查询明确列出需要的字段，避免将临时生成的seqnum混入结果

如果你的数据库支持布尔值排序（如PostgreSQL），可以简化排序条件：

select x.emp, x.checking, x.saving, x.cd, x.contact, x.type
from (
    select *,
           row_number() over (
               partition by emp, checking, saving, cd 
               order by (contact = 'no') desc
           ) as seqnum
    from table1
) x
where seqnum = 1

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