SQL按指定分组保留最新2个ReferencePeriod行，删除其余数据求助

解决方案 #

你之前的写法核心问题是窗口函数的分区逻辑和排名函数选择错误，修正方案如下：

1. 验证待清理数据（查询确认逻辑是否正确

WITH ranked_data AS (
    SELECT *,
        -- 按需求指定的SurveyCodeId、SurveyGroupCodeId分组，同组内按ReferencePeriod倒序排名
        -- DENSE_RANK保证同一个ReferencePeriod的所有行排名一致
        DENSE_RANK() OVER (
            PARTITION BY SurveyCodeId, SurveyGroupCodeId 
            ORDER BY ReferencePeriod DESC
        ) AS period_rank
    FROM Temp.tblKeepLastRefPeriod_MC
)
-- 排名大于2的即为需要删除的旧周期数据
SELECT * FROM ranked_data WHERE period_rank > 2;

2. 执行表清理操作

确认查询结果符合预期后，执行以下语句完成清理，仅保留每个分组最新2个ReferencePeriod的所有行：

WITH ranked_data AS (
    SELECT *,
        DENSE_RANK() OVER (
            PARTITION BY SurveyCodeId, SurveyGroupCodeId 
            ORDER BY ReferencePeriod DESC
        ) AS period_rank
    FROM Temp.tblKeepLastRefPeriod_MC
)
DELETE FROM ranked_data WHERE period_rank > 2;

适配兼容写法（部分不支持CTE直接删除的数据库可使用该方案

DELETE t
FROM Temp.tblKeepLastRefPeriod_MC t
INNER JOIN (
    SELECT SurveyCodeId, SurveyGroupCodeId, ReferencePeriod,
        DENSE_RANK() OVER (
            PARTITION BY SurveyCodeId, SurveyGroupCodeId 
            ORDER BY ReferencePeriod DESC
        ) AS period_rank
    FROM Temp.tblKeepLastRefPeriod_MC
) r ON t.SurveyCodeId = r.SurveyCodeId 
    AND t.SurveyGroupCodeId = r.SurveyGroupCodeId 
    AND t.ReferencePeriod = r.ReferencePeriod
WHERE r.period_rank > 2;

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