使用row_number()函数删除Players表重复记录并保留Games关联数据

需求说明 #

清理Players表中重复的姓名记录，遵循以下规则：

• 若某姓名存在与Games表关联的记录（即玩家ID出现在Games的PlayerID字段），必须保留至少一条关联记录；
• 同一姓名有多条关联记录时，可保留任意一条；
• 无关联记录的姓名，保留任意一条即可。

解决方案 #

利用ROW_NUMBER()函数对每个姓名分组排序，标记需要保留的记录，再删除其余重复项。以下是不同数据库的实现方式：

SQL Server / PostgreSQL 写法 #

WITH RankedPlayers AS (
    SELECT 
        ID,
        姓名,
        ROW_NUMBER() OVER (
            PARTITION BY 姓名 
            ORDER BY 
                -- 优先保留有Games关联的记录
                CASE WHEN EXISTS (SELECT 1 FROM Games g WHERE g.PlayerID = p.ID) THEN 0 ELSE 1 END,
                -- 同优先级下保留ID最小的记录，可改为ID DESC保留最大ID
                ID ASC
        ) AS rn
    FROM Players p
)
DELETE FROM RankedPlayers WHERE rn > 1;

MySQL 8.0+ 写法 #

WITH RankedPlayers AS (
    SELECT 
        ID,
        姓名,
        ROW_NUMBER() OVER (
            PARTITION BY 姓名 
            ORDER BY 
                CASE WHEN EXISTS (SELECT 1 FROM Games g WHERE g.PlayerID = p.ID) THEN 0 ELSE 1 END,
                ID ASC
        ) AS rn
    FROM Players p
)
DELETE p FROM Players p
JOIN RankedPlayers rp ON p.ID = rp.ID
WHERE rp.rn > 1;

代码解释 #

1. 分组排序：PARTITION BY 姓名将相同姓名的记录归为一组；
2. 优先级控制：ORDER BY中的CASE语句确保有Games关联的记录排在分组最前面，保证这类记录被优先保留；
3. 标记保留项：ROW_NUMBER()为每组内的记录编号，编号为1的是需要保留的记录；
4. 删除重复项：删除编号大于1的记录，只保留每组第一条。

示例验证 #

针对题目中的示例数据：

• Oliver组：ID2有Games关联，会被标记为rn=1，ID1被标记为rn=2并删除；
• Jack组：无关联记录，按ID ASC保留ID3（rn=1），ID4、5被标记为rn=2、3并删除；
• Harry组：ID6、7均有关联，按ID ASC保留ID6（rn=1），ID7被标记为rn=2并删除；
  最终结果与预期一致。

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