SQL Server 2018中如何移除日期范围冲突的旧数据条目

原始数据：

hmy     dtStart         dtEnd
14565   2016-07-01  2021-06-30 
17237   2016-10-01  2021-06-30
552400  2021-07-01  2021-09-30 
822316  2021-10-01  2024-12-30 

需求规则：当两条记录结束日期相同时，保留起始日期更晚（或hmy值更大）的记录，移除另一条。上述数据中14565和17237结束日期均为2021-06-30，需移除14565，期望输出：

hmy     dtStart         dtEnd
17237   2016-10-01  2021-06-30
552400  2021-07-01  2021-09-30 
822316  2021-10-01  2024-12-30

尝试的错误代码：

;WITH cte AS (
  SELECT hmy, dtStart, dtEnd,
         ROW_NUMBER() OVER (PARTITION BY dtStart ORDER BY dtEnd DESC) AS rn
  FROM #mytable
)

DELETE FROM #mytable
WHERE hmy IN (SELECT hmy FROM cte WHERE rn > 1)

解决方案：调整窗口函数的分区与排序规则 #

错误原因：原代码按dtStart分区，而冲突记录的dtStart不同，导致两条记录不在同一分区内，无法正确筛选要删除的条目。正确的分区键应该是dtEnd，同时按dtStart降序（或hmy降序）排序，确保同一结束日期组内保留符合要求的记录。

方法1：使用CTE删除

;WITH cte AS (
  SELECT hmy, dtStart, dtEnd,
         -- 按结束日期分组，同一组内先按起始日期倒序，再按hmy倒序排序
         ROW_NUMBER() OVER (PARTITION BY dtEnd ORDER BY dtStart DESC, hmy DESC) AS rn
  FROM #mytable
)
DELETE FROM cte WHERE rn > 1;

方法2：直接在DELETE语句中使用窗口函数（SQL Server 2017+支持）

DELETE FROM #mytable
WHERE hmy IN (
  SELECT hmy
  FROM (
    SELECT hmy,
           ROW_NUMBER() OVER (PARTITION BY dtEnd ORDER BY dtStart DESC, hmy DESC) AS rn
    FROM #mytable
  ) t
  WHERE rn > 1
);

代码说明 #

• PARTITION BY dtEnd：将所有结束日期相同的记录分到同一组
• ORDER BY dtStart DESC, hmy DESC：同一组内，优先保留起始日期更晚的记录；若起始日期有重复（示例中无此情况），则保留hmy更大的记录
• rn > 1：标记出每组中需要删除的非目标记录，执行删除即可得到期望结果

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