SQL删除表内重复记录时脚本始终返回0受影响行的逻辑排查

• 窗口函数返回的行号以1为起始，不存在值为0的行。原脚本中ToInclude字段是ROW_NUMBER()的计算结果，最小为1，WHERE条件里的ToInclude = 0永远不满足，这是脚本执行后返回0行受影响的最直接原因。
• 分区字段设计错误，错误将phonenumber纳入重复判定维度。按照业务规则，重复记录的判定依据是用户核心身份信息（姓氏、名字、出生日期、地址、城市、州省）一致，与手机号、有效期无关。原脚本把phonenumber作为分区字段，会导致同个用户下手机号为空的记录单独形成仅含1行的分区，永远无法触发RecordInstance > 1的删除条件。
• 分区字段缺失：原脚本的分区逻辑没有纳入city字段，由于测试表将地址、城市、州省拆分为独立字段，缺少城市字段参与分区会导致跨城市的同姓名、同街道地址、同州省的记录被误判为重复，也会影响重复识别的准确性。
• 窗口逻辑割裂，完全不符合预设的保留规则。原脚本用两个独立的窗口函数计算序号，没有把“优先保留手机号非空条目、再保留有效期最新记录”的优先级整合到同一套排序逻辑中，即使修正前三个问题，也无法实现预期的去重效果。

逻辑设计说明 #

1. 重复判定分区：仅使用用户核心身份字段作为分区依据，不纳入手机号、有效期这类用于计算保留优先级的字段，即PARTITION BY lastname, firstname, dateofbirth, address, city, state，确保同个用户的所有不同联系方式、不同有效期的记录都会被归为同一组进行比对。
2. 统一优先级排序：在同一分区内按规则排序，计算得到的ROW_NUMBER = 1即为需要保留的最优记录，序号大于1的即为需要清理的冗余记录。排序优先级为：
   • 第一优先级：有效手机号（非NULL且非空字符串）的记录排在前面
   • 第二优先级：同一手机号有效性下，有效期最新的记录排在前面
3. 如果是逻辑删除场景，可对冗余记录更新删除标记位；如果是物理删除场景，可直接删除冗余记录。

修正后代码示例 #

WITH RecordsToClean AS
(
    SELECT 
        -- 替换为表实际主键字段，方便后续定位记录
        id,
        ROW_NUMBER() OVER (
            PARTITION BY lastname, firstname, dateofbirth, address, city, state
            ORDER BY 
                -- 第一优先级：有效手机号排序在前，数值越大优先级越高
                CASE WHEN phonenumber IS NOT NULL AND phonenumber != '' THEN 1 ELSE 0 END DESC,
                -- 第二优先级：有效期最新的记录排序在前
                validitydate DESC
        ) AS KeepPriority
    FROM query
)
-- 逻辑删除场景使用以下语句：
-- UPDATE query 
-- SET is_deleted = TRUE 
-- WHERE id IN (SELECT id FROM RecordsToClean WHERE KeepPriority > 1)

-- 物理删除场景使用以下语句：
DELETE FROM RecordsToClean 
WHERE KeepPriority > 1

落地注意：如果业务场景中存在字符串字段前后空格、大小写不统一导致的重复识别漏判，可在分区、排序前对相关字段做TRIM()、UPPER()标准化处理，降低匹配误差。

用题目给出的测试样例验证，三条同身份用户的记录会被归为同一分区，最终排序结果为：

• 优先级1：手机号非空、有效期201902的记录，保留
• 优先级2：手机号非空、有效期201802的记录，删除
• 优先级3：手机号为空、有效期202009的记录，删除
  完全符合预设的去重规则。

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