MySQL中如何使用lead()函数取后2行值且最后两行不返回空值

问题原因 #

你碰到的倒数两行返回null是LEAD()函数的默认行为：当偏移量超出窗口剩余行数时，默认返回空值，原生LEAD()没有内置循环取数的逻辑，需要手动实现。

实现方案 #

可以通过行号计算+自关联的方式实现末尾循环从头取值的需求，步骤如下：

1. 给每行按业务需要的顺序分配唯一行号，同时统计全表总行数
2. 计算当前行偏移2位后的目标行号：如果当前行号+2超过总行数，减去总行数即可得到从头开始的对应行号
3. 通过自关联匹配到目标行的字段值即可

示例代码如下，替换对应字段、表名即可使用：

WITH numbered_table AS (
    SELECT 
        待取值的字段名,
        -- OVER()必须指定ORDER BY明确行顺序，否则结果不可控
        ROW_NUMBER() OVER(ORDER BY 你的排序字段) AS row_num,
        COUNT(*) OVER() AS total_count
    FROM 你的表名
)
SELECT 
    t1.待取值的字段名,
    t2.待取值的字段名 AS 向后偏移2行的字段值
FROM numbered_table t1
LEFT JOIN numbered_table t2 
ON t2.row_num = IF(t1.row_num + 2 > t1.total_count, t1.row_num + 2 - t1.total_count, t1.row_num + 2)
ORDER BY t1.row_num;

兼容说明 #

如果使用的是MySQL 8.0以下不支持CTE的版本，把CTE逻辑替换为子查询即可正常运行。

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