如何基于多字段分组更新表记录并选取每组首条记录

现有表结构 #

TargetDatabaseInfo表 #

TargetDatabaseId   TargetDatabaseName     ServerInfo
1                   SchoolDb               abc.123
2                   MyEmployee             pqr.123

ManagementRulesInfo表 #

ManagementRuleId    IsApplicable    TargetDatabaseId     BaseRuleId
11                    1               1                  101
12                    1               2                  101

ProcessSchedularInfo表 #

ProcessId      ProcessName    ExecuteOn     Status       ManagementRuleId
1               P1            2022-09-23    Pending       11
2               P2            2022-09-24    Pending       11
3               P3            2022-09-25    Pending       11
4               P1            2022-09-25    Pending       12

需求说明 #

将ProcessSchedularInfo表中，同一TargetDatabaseId和BaseRuleId组内的首条Pending状态记录的Status更新为Start，每组仅更新一条记录（示例中按ExecuteOn/ProcessId升序取最早的记录）。

预期更新结果 #

ProcessId      ProcessName    ExecuteOn     Status       ManagementRuleId
1               P1            2022-09-23    Start         11
2               P2            2022-09-24    Pending       11
3               P3            2022-09-25    Pending       11
4               P1            2022-09-25    Start         12

现有SQL的问题 #

当前编写的SQL仅做了表关联和分组，但未实现每组选取首条Pending记录的逻辑，会错误更新组内所有关联记录，不符合需求。

正确的SQL实现 #

可以通过窗口函数ROW_NUMBER()对每组记录排序，筛选出每组首条记录后执行更新：

适用于SQL Server等支持UPDATE CTE的数据库 #

WITH RankedProcesses AS (
    SELECT 
        p.ProcessId,
        p.Status,
        ROW_NUMBER() OVER (
            PARTITION BY m.TargetDatabaseId, m.BaseRuleId 
            ORDER BY p.ExecuteOn ASC, p.ProcessId ASC
        ) AS RowNum
    FROM ProcessSchedularInfo p
    INNER JOIN ManagementRulesInfo m 
        ON p.ManagementRuleId = m.ManagementRuleId
    WHERE p.Status = 'Pending'
)
UPDATE RankedProcesses
SET Status = 'Start'
WHERE RowNum = 1;

适用于MySQL 8.0+等数据库 #

UPDATE ProcessSchedularInfo p
JOIN (
    SELECT 
        p_inner.ProcessId,
        ROW_NUMBER() OVER (
            PARTITION BY m.TargetDatabaseId, m.BaseRuleId 
            ORDER BY p_inner.ExecuteOn ASC, p_inner.ProcessId ASC
        ) AS RowNum
    FROM ProcessSchedularInfo p_inner
    INNER JOIN ManagementRulesInfo m 
        ON p_inner.ManagementRuleId = m.ManagementRuleId
    WHERE p_inner.Status = 'Pending'
) ranked ON p.ProcessId = ranked.ProcessId
SET p.Status = 'Start'
WHERE ranked.RowNum = 1;

逻辑说明 #

1. 用窗口函数ROW_NUMBER()按TargetDatabaseId和BaseRuleId分组，每组内按ExecuteOn（执行时间）、ProcessId升序排序，为每条记录分配行号。
2. 筛选出每组行号为1的记录（组内首条Pending记录），将其Status更新为Start。

内容的提问来源于stack exchange，提问作者I Love Stackoverflow