如何在SQLite中修改CTE查询删除ROW_NUMBER识别的重复数据

你出现报错的核心原因是绝大多数数据库不支持直接对包含窗口函数的CTE（临时结果集）执行DELETE操作，可根据你使用的数据库类型选择对应方案：

方案1：适配支持CTE可写的数据库（PostgreSQL、SQL Server、MySQL 8.0及以上版本） #

通过唯一标识关联删除，代码如下：

WITH RowNumCTE AS (
SELECT *, 
    ROW_NUMBER() OVER (
        PARTITION BY ParcelID,
                     PropertyAddress,
                     SalePrice,
                     SaleDate,
                     LegalReference
                     ORDER BY 
                     UniqueID
                ) row_num
FROM housing_data
)
DELETE FROM housing_data
WHERE UniqueID IN (
    SELECT UniqueID 
    FROM RowNumCTE
    WHERE row_num > 1
);

如果你的数据库支持单表CTE直接删除，也可以简化为DELETE FROM RowNumCTE WHERE row_num > 1，执行前可以先替换成SELECT语句验证结果是否符合预期。

方案2：全数据库通用兼容方案 #

不需要依赖CTE支持，用自关联逻辑实现去重，保留分组内UniqueID最小的行：

DELETE t1
FROM housing_data t1
INNER JOIN housing_data t2
  ON t1.ParcelID = t2.ParcelID
  AND t1.PropertyAddress = t2.PropertyAddress
  AND t1.SalePrice = t2.SalePrice
  AND t1.SaleDate = t2.SaleDate
  AND t1.LegalReference = t2.LegalReference
  AND t1.UniqueID > t2.UniqueID;

注意事项 #

• 执行任何删除操作前，务必先把DELETE替换成SELECT查询，确认待删除的行符合预期，避免误删数据
• 建议操作前先对housing_data表做全量备份
• 数据量较大时建议分批执行删除，避免长时间锁表影响业务

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