如何使用带Partition的Case语句按规则更新每个ID的Status字段

针对你提到的「同一ID对应多个Status值，需要按条件筛选目标状态并更新」的问题，我结合你给出的表结构和示例数据，整理了几种常见场景的解决方案：

场景1：按Status优先级确定目标值并批量更新 #

如果你的Status有明确的优先级（比如 Approved > Modified > Later），我们可以先为每个ID锁定优先级最高的Status，再完成更新：

第一步：验证目标Status（先查再更更稳妥） #

先用窗口函数给每个ID的Status按优先级排序，找出目标值：

WITH RankedStatus AS (
    SELECT 
        ID,
        status,
        -- 按自定义优先级排序，数字越小优先级越高
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 
            CASE status 
                WHEN 'Approved' THEN 1 
                WHEN 'Modified' THEN 2 
                WHEN 'Later' THEN 3 
                ELSE 4 -- 其他未定义状态排最后
            END) AS rn
    FROM Table1
)
SELECT ID, status AS TargetStatus
FROM RankedStatus
WHERE rn = 1;

第二步：执行更新操作 #

用上面的查询结果批量更新原表中对应ID的所有Status：

WITH RankedStatus AS (
    SELECT 
        ID,
        status,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 
            CASE status 
                WHEN 'Approved' THEN 1 
                WHEN 'Modified' THEN 2 
                WHEN 'Later' THEN 3 
                ELSE 4 
            END) AS rn
    FROM Table1
)
UPDATE t1
SET t1.status = rs.TargetStatus
FROM Table1 t1
INNER JOIN (
    SELECT ID, status AS TargetStatus
    FROM RankedStatus
    WHERE rn = 1
) rs ON t1.ID = rs.ID;

场景2：按日期字段取最新的Status更新 #

如果你的筛选条件是取最新创建/审批的记录的Status（比如依赖CreateDate或ApprovalDate），可以这样写：

WITH LatestStatus AS (
    SELECT 
        ID,
        status,
        -- 优先用ApprovalDate，为空则用CreateDate，按降序取最新的一条
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ISNULL(ApprovalDate, CreateDate) DESC) AS rn
    FROM Table1
)
UPDATE t1
SET t1.status = ls.status
FROM Table1 t1
INNER JOIN LatestStatus ls ON t1.ID = ls.ID
WHERE ls.rn = 1;

你可以根据实际业务需求调整排序的字段和顺序（比如改成升序取最早的记录）。

场景3：仅保留目标Status记录（删除冗余数据） #

如果你不需要保留同一ID的其他Status记录，只想留下符合条件的那条，可执行删除操作：

WITH RankedStatus AS (
    SELECT 
        ID,
        status,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 
            CASE status 
                WHEN 'Approved' THEN 1 
                WHEN 'Modified' THEN 2 
                WHEN 'Later' THEN 3 
                ELSE 4 
            END) AS rn
    FROM Table1
)
DELETE FROM RankedStatus
WHERE rn > 1;

关键提示 #

• 不管是更新还是删除，一定要先运行对应的SELECT语句验证结果，确认符合预期后再执行写操作，避免误改数据。
• 如果有额外的筛选条件（比如特定日期范围、其他字段的限制），直接在CTE的WHERE子句中添加即可。

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