MySQL 查找连续相同名称的记录组，并保留每组内时间最大的一条记录

要求：查找连续相同名称的记录组，并保留每组内时间最大的一条记录，同时计算每组记录的 num 总和。

今天有人问了我一个问题，大致就是下面这样的数据结构（原谅我实在不知道怎么描述这个问题）

然后需要得到下面这样的结果

直接上SQL，因为这个也不是我能写出来的，是我跟 GPT 磕了半个小时才得到的，这里只是做个记录防止下次要用又不知道怎么描述，所以大家要是需要直接用但是不要问我具体实现逻辑

WITH GroupedProcesses AS (
    SELECT 
        id, 
        name, 
        create_time, 
        num,
        -- 如果相邻两条记录的 name 不同，则标记新组
        CASE 
            WHEN name = LAG(name) OVER (ORDER BY id) 
            THEN 0 
            ELSE 1 
        END AS isNewGroup
    FROM table_ceshi
),
NumberedGroups AS (
    SELECT 
        id, 
        name, 
        create_time, 
        num,
        -- 根据 isNewGroup 的累加值生成组编号
        SUM(isNewGroup) OVER (ORDER BY id) AS groupNum
    FROM GroupedProcesses
),
MaxTimePerGroup AS (
    -- 找出每组内 create_time 最大的记录
    SELECT 
        groupNum, 
        name, 
        MAX(create_time) AS max_time
    FROM NumberedGroups
    GROUP BY groupNum, name
)
SELECT 
    g.id,
    g.name, 
    m.max_time AS create_time, 
    SUM(g.num) AS totalNum -- 按组计算 num 的和
FROM NumberedGroups g
JOIN MaxTimePerGroup m 
  ON g.groupNum = m.groupNum AND g.name = m.name
GROUP BY g.groupNum, g.name, m.max_time
ORDER BY m.max_time;

2024-11-15更新
看来 GPT4 还是比 3.5 强大不少，很快就能给出答案还更简洁

WITH GroupedData AS (
    SELECT 
        id,
        name,
        create_time,
        num,
        ROW_NUMBER() OVER (ORDER BY id) - ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS grp
    FROM 
        table_ceshi
)
SELECT 
    MIN(id) AS id,
    name,
    MAX(create_time) AS create_time,
    SUM(num) AS totalNum
FROM 
    GroupedData
GROUP BY 
    name, grp
ORDER BY 
    id;