如何在SQL中保留ISBIGCHEESE=1的记录并去重PAYMENTID重复行

问题背景 #

现有报表表dbo.HighDonorPayments数据如下：

ID   PAYMENTID  DONORID  MEMBERFIRSTNAME   MEMBERLASTNAME    ISBIGCHEESE
--------------------------------------------------------------------------
1    4133-ggi     661a   Samuel            Jackson           1
2    4133-ggi     661a   Samuel            Jackson           0          
3    4133-ggi     661a   Samuel            Jackson           0        
4    9392-ggi     990q   Space             Eye               0
5    9392-ggi     990q   Space             Eye               0

因关联捐赠者分类表产生了PAYMENTID重复的记录，需要按以下规则去重：

• 同一PAYMENTID的重复行，若存在ISBIGCHEESE=1的记录，保留该行并删除其他行
• 无ISBIGCHEESE=1标识的重复行，任意保留一条

原使用CTE去重的代码无法实现优先保留ISBIGCHEESE=1的记录，保留结果具有随机性：

WITH eye_cte AS 
(
    SELECT 
        paymentid, 
        ROW_NUMBER() OVER (PARTITION BY paymentid ORDER BY revenueid) row_num 
    FROM 
        dbo.HighDonorPayments
) 
DELETE FROM eye_cte 
WHERE row_num > 1;

解决方案：修改CTE的排序逻辑 #

通过调整ROW_NUMBER()窗口函数的排序规则，优先将ISBIGCHEESE=1的记录排在分组首位，确保其被保留。修改后的代码如下：

WITH ranked_payments AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY PAYMENTID 
            ORDER BY ISBIGCHEESE DESC, ID ASC -- 优先保留ISBIGCHEESE=1的记录，无该标识时按ID升序保留
        ) AS row_num
    FROM dbo.HighDonorPayments
)
DELETE FROM ranked_payments 
WHERE row_num > 1;

逻辑说明 #

• PARTITION BY PAYMENTID：按支付ID分组处理重复记录
• ORDER BY ISBIGCHEESE DESC：将ISBIGCHEESE=1的行排在分组的第一位，对应的row_num=1不会被删除
• 后续的ID ASC（可替换为其他字段）：在无ISBIGCHEESE=1的分组里，确保保留的记录是确定的，避免随机结果

数据填充逻辑优化建议 #

如果重复记录是关联捐赠者分类表时产生的，建议在数据填充阶段就避免重复：

• 关联表时使用OUTER APPLY结合TOP 1，优先选取ISBIGCHEESE=1的分类记录，确保每个PAYMENTID只生成一条记录
• 插入数据前先对源数据按规则去重，筛选出唯一的PAYMENTID记录后再插入，减少后续去重操作，提升整体效率

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