SQL Server 2014中按规则处理重复行并更新Col 6的需求

给定数据表（或排序后的临时表），需按以下规则更新Col6字段：

1. 当Col1-Col4匹配且Col5全为0时，保留任意一行Col6为0，其余行Col6设为1；
2. 当Col1-Col4匹配且Col5既有大于0的值又有0时，所有Col5=0的行Col6设为1；
3. 当Col1-Col4匹配且Col5均大于0时，不修改Col6。

解决方案（无需游标，用窗口函数实现） #

WITH GroupStats AS (
    SELECT 
        Uid,
        Col1, Col2, Col3, Col4, Col5, Col6,
        -- 标记分组内是否存在Col5>0的行
        MAX(CASE WHEN Col5 > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY Col1, Col2, Col3, Col4) AS HasNonZero,
        -- 标记分组内是否包含0值（因Col5无负数，Min=0则存在0）
        MIN(Col5) OVER (PARTITION BY Col1, Col2, Col3, Col4) AS MinCol5,
        -- 给分组内的行编号，用于保留任意一行（此处按Uid升序，保留最小Uid的行）
        ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3, Col4 ORDER BY Uid) AS RowNum
    FROM YourTempTable -- 替换为你的表名/临时表名
)
UPDATE GroupStats
SET Col6 = 1
WHERE 
    -- 匹配规则2：分组有非0值，且当前行Col5为0
    (HasNonZero = 1 AND Col5 = 0)
    OR
    -- 匹配规则1：分组全为0，且不是分组内保留的第一行
    (MinCol5 = 0 AND HasNonZero = 0 AND RowNum > 1);

逻辑说明 #

1. CTE分组统计：
   • HasNonZero：通过窗口函数的MAX判断当前Col1-Col4分组内是否存在Col5>0的行，存在则为1，否则为0；
   • MinCol5：利用Col5无负数的特性，MIN(Col5)=0说明分组内包含0值；
   • RowNum：给每个分组内的行按Uid编号，用来在全0分组中指定保留的行（可修改ORDER BY子句选择其他行，比如ORDER BY Uid DESC保留最大Uid的行）。
2. 更新条件：
   • 规则2的场景：分组存在非0值，且当前行Col5=0，直接将Col6设为1；
   • 规则1的场景：分组全为0（MinCol5=0且HasNonZero=0），且不是分组内的第一行，将Col6设为1；
   • 规则3的场景：分组内HasNonZero=1且MinCol5>0（全为非0值），不会进入更新条件，保持Col6不变。

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