SQL Server中CTE用COUNT窗口函数执行删除时报4403错误的原因

报错原因 #

SQL Server 对可更新CTE有严格限制：要求CTE返回的每一行必须和底层原表的行是一一对应的关系，数据库才能将对CTE的删除操作映射到原表的对应记录。
你的CTE中使用了DISTINCT去重，还使用了窗口聚合函数COUNT(*) OVER()做聚合计算，已经破坏了这种一一映射关系，所以无法直接对这个CTE执行删除操作，和窗口函数本身的使用没有直接关联，报错提示里的描述也明确说明了这个限制。

正确实现方案 #

你要删除id对应的条目数不等于31的所有记录，可以用下面两种写法实现：

方案1：通过分组先筛选出要删除的id #

WITH IdToDelete AS
(
    SELECT id
    FROM dailyActivity_merged$
    GROUP BY id
    HAVING COUNT(*) <> 31
)
DELETE FROM dailyActivity_merged$
WHERE id IN (SELECT id FROM IdToDelete)

方案2：将窗口函数计算结果关联回原表删除 #

DELETE t
FROM dailyActivity_merged$ t
JOIN (
    SELECT 
        id,
        COUNT(*) OVER (PARTITION BY id) AS ctn
    FROM dailyActivity_merged$
) AS cnt_table ON t.id = cnt_table.id
WHERE cnt_table.ctn <> 31

内容的提问来源于stack exchange，提问作者Emil-Constantin BUCUR