MySQL pre-8.0按指定字段分组获取连续记录对的实现方法咨询

实现思路 #

• 核心要解决两个问题：一是拿到同name分组下，比当前行record_id大的第一条记录的ID；二是通过左连接自动给每组最后一条记录填充NULL值
• 因为没有LEAD()窗口函数，用关联子查询取同组下大于当前record_id的最小ID，就能等价实现下一条记录的定位，配合name、record_id上的索引，查询效率有保障

完整实现SQL #

把SQL里的your_table替换成你实际的表名即可：

SELECT
    base.curr_id,
    base.curr_name,
    base.curr_payload,
    t_next.record_id AS next_record_id,
    t_next.name AS next_name,
    t_next.payload AS next_payload
FROM (
    SELECT 
        t1.record_id AS curr_id,
        t1.name AS curr_name,
        t1.payload AS curr_payload,
        (
            SELECT MIN(t2.record_id)
            FROM your_table t2
            WHERE t2.name = t1.name 
              AND t2.record_id > t1.record_id
        ) AS next_id
    FROM your_table t1
) base
LEFT JOIN your_table t_next
    ON base.next_id = t_next.record_id
ORDER BY base.curr_name, base.curr_id;

逻辑说明 #

1. 内层子查询的作用是遍历原表每一行，对每一行查找同name下record_id比当前行大的最小值，这个值就是同组内紧接当前行的下一条记录的主键
2. 外层用LEFT JOIN关联原表拿取下一条记录的完整字段，每组最后一条记录因为没有下一条，next_id为NULL，关联后对应的下一条记录字段自然返回NULL，不需要额外补值
3. 最后加的ORDER BY规则是为了输出顺序和你给出的样例完全一致，可根据实际需求调整

结果验证 #

用你提供的样例数据执行上述SQL，返回结果完全匹配预期：

1 a x 5 a z
5 a z 6 a w
6 a w NULL NULL NULL
2 b y 4 b z
4 b z 7 b y
7 b y NULL NULL NULL
3 c y 8 c x
8 c x NULL NULL NULL

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