SQL Server中使用CTE结合Delete语句删除重复记录失败问题排查

嗨，咱们先把问题拆解清楚——你遇到的报错不是CTE作用域的问题，核心原因是你的CTE没有包含能让SQL Server定位到原表具体行的信息，而且写法上有个关键疏漏。

为什么SELECT能跑，DELETE却报错？ #

当你执行注释掉的SELECT ROWNUM FROM CTE_BASE时，只是读取CTE计算出的rownum值，完全不需要关联回原表；但DELETE操作不一样，它需要明确知道要删除Salary表里的哪一行，可你的CTE只生成了rownum，没有任何和原表行绑定的标识（比如EmpId），SQL Server根本不知道该删哪些记录，自然就报错了。

另外，你的ROW_NUMBER()排序逻辑也有点问题：PARTITION BY SALARY ORDER BY SALARY DESC里，相同薪资的记录排序依据还是薪资，这会导致排序结果不稳定，最好换成用唯一标识（比如EmpId）来排序，这样重复行的顺序是明确的，方便你控制保留哪一行。

正确的可执行写法 #

你需要在CTE里包含原表的主键（EmpId）以及计算出的rownum，这样DELETE操作就能准确关联到原表的行：

BEGIN TRAN
WITH CTE_BASE AS (
 SELECT 
  EmpId,
  Salary,
  -- 用EmpId排序，确保同一薪资组的行有稳定的顺序，这里默认保留EmpId最小的行
  ROW_NUMBER() OVER (PARTITION BY SALARY ORDER BY EmpId) AS rownum
 FROM Salary
)
-- 现在CTE包含EmpId，能直接映射回原表执行删除
DELETE FROM CTE_BASE WHERE rownum > 1;

-- 可以执行这句验证删除后的结果
-- SELECT * FROM Salary;

ROLLBACK TRAN

额外说明 #

• 用CTE执行DELETE/UPDATE时，CTE必须是可更新CTE：简单说就是CTE要能直接映射到基表的行，不能是聚合、分组后的结果（特殊场景除外），而且必须包含基表的唯一标识，让SQL Server能找到要修改的具体行。
• 如果你想保留每个薪资组里EmpId最大的行，只需要把ORDER BY EmpId改成ORDER BY EmpId DESC就行，这样rownum>1的就是要删除的重复行。

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