在MySQL的实际业务开发中，查询特定记录的前后相邻行是一个常见需求，比如电商系统中查看某笔订单的上一条和下一条订单，或者日志系统中查看某条日志的前后相邻日志。MySQL 8.0版本引入的LEAD和LAG窗口函数可以非常高效地实现这个需求，避免了传统自连接或者子查询的复杂逻辑。
LEAD与LAG函数基本语法
LEAD和LAG都属于窗口函数的一种，核心作用是获取当前行在排序后的结果集中，前一行或者后一行的对应字段值。
LAG函数语法
LAG函数用于获取当前行之前指定偏移量的行的字段值，语法如下：
LAG(字段名, 偏移量, 默认值) OVER (PARTITION BY 分组字段 ORDER BY 排序字段 [ASC|DESC])
参数说明：
- 字段名：需要获取的字段
- 偏移量：往前数的行数，默认是1，表示前一行
- 默认值：如果往前没有对应行时返回的值，默认是NULL
- PARTITION BY：可选，用于分组，每个分组内单独计算
- ORDER BY：必填，指定排序规则，相邻行基于这个排序确定
LEAD函数语法
LEAD函数用于获取当前行之后指定偏移量的行的字段值，语法和LAG类似：
LEAD(字段名, 偏移量, 默认值) OVER (PARTITION BY 分组字段 ORDER BY 排序字段 [ASC|DESC])
参数含义和LAG函数一致，偏移量默认是1，表示后一行。
查询特定记录前后相邻行的实现步骤
假设我们有一张订单表order_info，表结构如下：
CREATE TABLE order_info (
id INT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
user_id INT NOT NULL,
order_amount DECIMAL(10,2) NOT NULL,
create_time DATETIME NOT NULL
);
现在需要查询用户ID为1001的所有订单，并且获取每笔订单的上一条和下一条订单的订单号，按照创建时间升序排列。
第一步：给所有记录添加前后相邻行标识
首先使用LEAD和LAG函数给每个订单加上前一行和后一行的订单号，先筛选出用户1001的订单：
SELECT
id,
order_no,
order_amount,
create_time,
-- 获取前一行订单号，没有则返回'无'
LAG(order_no, 1, '无') OVER (ORDER BY create_time ASC) AS prev_order_no,
-- 获取后一行订单号，没有则返回'无'
LEAD(order_no, 1, '无') OVER (ORDER BY create_time ASC) AS next_order_no
FROM order_info
WHERE user_id = 1001
ORDER BY create_time ASC;
执行以上查询后，会得到每个订单对应的前一条和后一条订单号，结果示例如下：
| id | order_no | order_amount | create_time | prev_order_no | next_order_no |
|---|---|---|---|---|---|
| 1 | ORD20240101001 | 199.00 | 2024-01-01 10:00:00 | 无 | ORD20240102001 |
| 2 | ORD20240102001 | 299.00 | 2024-01-02 14:30:00 | ORD20240101001 | ORD20240103001 |
| 3 | ORD20240103001 | 399.00 | 2024-01-03 09:15:00 | ORD20240102001 | 无 |
第二步：筛选特定记录的前后相邻行
如果只需要查询某笔特定订单的前后相邻行，比如订单号为ORD20240102001的前后相邻订单，可以将上面的查询作为子查询，再添加筛选条件：
SELECT *
FROM (
SELECT
id,
order_no,
order_amount,
create_time,
LAG(order_no, 1, '无') OVER (ORDER BY create_time ASC) AS prev_order_no,
LEAD(order_no, 1, '无') OVER (ORDER BY create_time ASC) AS next_order_no
FROM order_info
WHERE user_id = 1001
) t
WHERE t.order_no = 'ORD20240102001';
执行后就可以得到该特定订单的前一行订单号和后一行订单号，如果需要获取完整的前后相邻行记录，可以再关联原表查询：
SELECT
o1.*,
o2.order_no AS prev_order_no,
o2.order_amount AS prev_order_amount,
o3.order_no AS next_order_no,
o3.order_amount AS next_order_amount
FROM order_info o1
-- 关联前一行记录
LEFT JOIN order_info o2
ON o2.order_no = (
SELECT LAG(order_no, 1, NULL) OVER (ORDER BY create_time ASC)
FROM order_info
WHERE order_no = 'ORD20240102001'
LIMIT 1
)
-- 关联后一行记录
LEFT JOIN order_info o3
ON o3.order_no = (
SELECT LEAD(order_no, 1, NULL) OVER (ORDER BY create_time ASC)
FROM order_info
WHERE order_no = 'ORD20240102001'
LIMIT 1
)
WHERE o1.order_no = 'ORD20240102001';
使用注意事项
- LEAD和LAG函数是MySQL 8.0及以上版本才支持的功能，如果使用低版本MySQL，无法直接使用这两个函数，需要通过自连接或者变量实现类似功能。
- 排序规则必须明确，OVER子句中的ORDER BY是确定相邻行的核心，如果排序字段有重复值，可能会导致相邻行结果不符合预期，建议排序字段使用唯一值或者组合排序字段保证顺序唯一。
- 如果使用了PARTITION BY分组，那么相邻行只在当前分组内计算，不会跨分组获取，比如按用户ID分组后，每个用户的订单只会找该用户自己的前后订单，不会和其他用户的订单混淆。
- 偏移量必须是非负整数，如果设置为0，那么获取的就是当前行的字段值。
低版本MySQL替代方案
如果使用的是MySQL 5.7及以下版本，没有窗口函数支持，可以通过用户变量实现前后相邻行的查询，示例如下：
-- 先给排序后的记录添加行号
SET @row_num = 0;
SELECT
id,
order_no,
order_amount,
create_time,
@row_num := @row_num + 1 AS row_num
FROM order_info
WHERE user_id = 1001
ORDER BY create_time ASC;
-- 再通过行号关联获取前后行
SELECT
t1.*,
t2.order_no AS prev_order_no,
t3.order_no AS next_order_no
FROM (
SELECT
id,
order_no,
order_amount,
create_time,
@row_num := @row_num + 1 AS row_num
FROM order_info, (SELECT @row_num := 0) r
WHERE user_id = 1001
ORDER BY create_time ASC
) t1
LEFT JOIN (
SELECT
id,
order_no,
order_amount,
create_time,
@row_num2 := @row_num2 + 1 AS row_num
FROM order_info, (SELECT @row_num2 := 0) r
WHERE user_id = 1001
ORDER BY create_time ASC
) t2 ON t2.row_num = t1.row_num - 1
LEFT JOIN (
SELECT
id,
order_no,
order_amount,
create_time,
@row_num3 := @row_num3 + 1 AS row_num
FROM order_info, (SELECT @row_num3 := 0) r
WHERE user_id = 1001
ORDER BY create_time ASC
) t3 ON t3.row_num = t1.row_num + 1
WHERE t1.order_no = 'ORD20240102001';
这种方式逻辑相对复杂，执行效率也不如窗口函数，建议在条件允许的情况下升级MySQL版本使用LEAD和LAG函数。