如何在SQL查询中获取同列表的prev_id与next_id？

以下是我的尝试代码：

SELECT 
    p.id,
    '' as next_id,
    '' as prev_id,
    u.name,
    u.id,
    t.truck_number,
    p.check_date,
    p.* 
FROM 
         eaglex_crm.pro   p
    JOIN eaglex_crm.truck t ON t.id = p.truck_id
    JOIN eaglex_crm.users u on u.id = p.driver_id
WHERE true
order by u.id, t.id, p.check_date

当前输出中，prev_id和next_id字段为空，其余字段可正常显示司机、车辆及检查记录的相关信息。我希望能在查询结果中返回同列表里的上一条ID（prev_id）和下一条ID（next_id），请协助实现。

解决方案 #

可以使用SQL窗口函数LAG()和LEAD()实现需求，这两个函数能在有序结果集中获取当前行的上一行和下一行数据。结合你的排序规则，修改后的查询语句如下：

SELECT 
    p.id,
    -- 获取下一条记录的ID，按司机、车辆、检查日期排序
    LEAD(p.id) OVER (PARTITION BY u.id, t.id ORDER BY p.check_date) AS next_id,
    -- 获取上一条记录的ID，按司机、车辆、检查日期排序
    LAG(p.id) OVER (PARTITION BY u.id, t.id ORDER BY p.check_date) AS prev_id,
    u.name AS driver_name,
    u.id AS driver_id,
    t.truck_number,
    p.check_date,
    p.* 
FROM 
         eaglex_crm.pro   p
    JOIN eaglex_crm.truck t ON t.id = p.truck_id
    JOIN eaglex_crm.users u ON u.id = p.driver_id
ORDER BY u.id, t.id, p.check_date

说明 #

• LAG(p.id) OVER (PARTITION BY u.id, t.id ORDER BY p.check_date)：在同一司机（u.id）、同一车辆（t.id）的分组内，按检查日期排序，获取当前记录的上一条ID。若为分组内第一条记录，prev_id返回NULL。
• LEAD(p.id) OVER (PARTITION BY u.id, t.id ORDER BY p.check_date)：同理，获取当前记录的下一条ID。若为分组内最后一条记录，next_id返回NULL。
• 若无需按司机和车辆分组，要在整个结果集中获取上/下一条ID，去掉PARTITION BY u.id, t.id部分即可。

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