SQL Server 2019：删除重复员工记录并保留最新记录的查询需求

环境：Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64)

现有查询用于找出所有EmployeeID重复的员工记录：

SELECT
    ID
    ,EmployeeID
    ,EmployeeName
    ,BadgeNumber
    ,EffectiveDate
FROM
    [MyDatabase].[dbo].[MyEmployeeTable]
WHERE
EmployeeID IN(
    SELECT EmployeeID
    FROM [MyDatabase].[dbo].[MyEmployeeTable]
    GROUP BY EmployeeID
    HAVING COUNT(*) > 1
)
ORDER BY EmployeeID, EffectiveDate DESC

该查询的模拟结果：

ID      EmployeeID  EmployeeName            BadgeNumber     EffectiveDate
3822    10000001    Example Employee A      19023           2023-07-08
2232    10000001    Example Employee A      19248           2022-01-02
3946    10000005    Example Employee B      19212           2022-01-23
4189    10000005    Example Employee B      19029           2021-11-15
4176    10000005    Example Employee B      19002           2021-11-10
3820    10000010    Example Employee C      12070           2022-03-27
2625    10000010    Example Employee C      19005           2021-11-15
4682    10000055    Example Employee D      12751           2023-01-05
3664    10000055    Example Employee D      12767           2021-11-29

需求 #

需要筛选出每个员工除最新记录（按EffectiveDate字段判断）外的所有重复项，目标结果如下：

ID      EmployeeID  EmployeeName            BadgeNumber     EffectiveDate
2232    10000001    Example Employee A      19248           2022-01-02
4189    10000005    Example Employee B      19029           2021-11-15
4176    10000005    Example Employee B      19002           2021-11-10
2625    10000010    Example Employee C      19005           2021-11-15
3664    10000055    Example Employee D      12767           2021-11-29

解决方案 #

使用ROW_NUMBER()窗口函数为每个EmployeeID分组的记录按EffectiveDate降序编号，编号为1的即为最新记录，筛选编号大于1的即可得到需删除的重复项：

1. 筛选待删除记录

SELECT ID, EmployeeID, EmployeeName, BadgeNumber, EffectiveDate
FROM (
    SELECT 
        ID,
        EmployeeID,
        EmployeeName,
        BadgeNumber,
        EffectiveDate,
        -- 按EmployeeID分组，按EffectiveDate降序生成行号
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY EffectiveDate DESC) AS RowNum
    FROM [MyDatabase].[dbo].[MyEmployeeTable]
) AS RankedEmployees
WHERE RowNum > 1
ORDER BY EmployeeID, EffectiveDate DESC;

2. 直接删除重复记录

如果确认筛选结果正确，可结合CTE直接删除重复项（执行前建议备份数据）：

WITH RankedEmployees AS (
    SELECT 
        ID,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY EffectiveDate DESC) AS RowNum
    FROM [MyDatabase].[dbo].[MyEmployeeTable]
)
DELETE FROM RankedEmployees WHERE RowNum > 1;

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