SQL按同Id下duration最大值设置max_register字段的实现方法

方案结论 #

完全不需要使用游标，几千条的数据量很小，用基于集合的SQL操作效率比游标高得多，实现也更简洁。

常用实现方案（主流数据库通用思路） #

方案1：关联子查询更新（兼容性最好，几乎所有数据库都支持） #

先按Id分组拿到每个Id对应的最大duration值，再关联原表更新对应行的max_register字段即可：

-- 初始值全为0的话可以省略这步
UPDATE 你的临时表名 SET max_register = 0;

-- 给每个Id对应的最大duration行设为1
UPDATE 你的临时表名 t1
SET max_register = 1
WHERE (t1.Id, t1.duration) IN (
    SELECT Id, MAX(duration) AS max_duration
    FROM 你的临时表名
    GROUP BY Id
);

如果你的数据库不支持行构造器的IN语法，可以改成关联更新的写法：

UPDATE 你的临时表名 t1
INNER JOIN (
    SELECT Id, MAX(duration) AS max_duration
    FROM 你的临时表名
    GROUP BY Id
) t2 ON t1.Id = t2.Id AND t1.duration = t2.max_duration
SET t1.max_register = 1;

方案2：窗口函数实现（支持MySQL 8+/PostgreSQL/SQL Server等新版数据库） #

如果是支持窗口函数的数据库版本，写法更灵活，还能处理同Id下有多个相同duration最大值的场景：

WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY duration DESC) AS rank_num
    FROM 你的临时表名
)
UPDATE 你的临时表名 t1
INNER JOIN ranked_data t2 
    ON t1.Id = t2.Id 
    AND t1.duration = t2.duration 
    AND t2.rank_num = 1
SET t1.max_register = 1;

注：如果同组有多个相同的最大duration，ROW_NUMBER()只会选其中一行设为1，要所有最大值行都设1的话把ROW_NUMBER()改成RANK()即可。

为什么不建议用游标？ #

游标是逐行处理的逻辑，几千条数据虽然也能运行，但性能远不如原生的集合操作，代码也更冗长，还需要额外处理异常、游标关闭等问题，完全没必要在这个场景下使用。

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