如何使用Over Partition按Register分组为最大minutes行ID置1其余置0

原有代码存在的问题 #

• 分区键不符合需求：要求按Register字段分组，原代码错误使用了PARTITION BY minutes
• 排序逻辑错误：要取每组minutes的最大值，排序应该用降序DESC，原代码用升序ASC只会取到每组最小值
• 多余的TOP 1限制：添加该关键字后只会返回全表第一行数据，无法获取每个分组的最大值行
• 缺少其余行置0逻辑：如果表中ID初始值不是全0，仅更新置1的逻辑会导致结果错误

正确实现方案 #

方案1：分组关联更新（兼容性高，支持所有主流SQL数据库） #

先更新每个分组内minutes最大的行ID为1：

UPDATE 你的表名 t1
INNER JOIN (
    SELECT Register, MAX(minutes) AS max_minutes
    FROM 你的表名
    GROUP BY Register
) t2 ON t1.Register = t2.Register AND t1.minutes = t2.max_minutes
SET t1.ID = 1;

再更新剩余行ID为0：

UPDATE 你的表名
SET ID = 0
WHERE ID != 1;

方案2：窗口函数一次性更新（适合支持CTE和窗口函数的数据库，如SQL Server、MySQL 8.0+、PostgreSQL等） #

WITH group_ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY Register ORDER BY minutes DESC) AS rank_num
    FROM 你的表名
)
UPDATE group_ranked
SET ID = CASE WHEN rank_num = 1 THEN 1 ELSE 0 END;

特殊场景说明：如果同一个分组内存在多个相同的最大minutes值，ROW_NUMBER()只会随机选一行设为1；如果需要所有最大值行都设为1，将ROW_NUMBER()替换为RANK()即可。

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