MySQL查询当前数据的上一条，下一条？

Question

获取当前数据的上一条，下一条的 MySQL 代码:

WITH RankedRecords AS (    
    SELECT *,    
           LEAD(id) OVER (ORDER BY time ASC) AS next_id,    
           LAG(id) OVER (ORDER BY time ASC) AS prev_id    
    FROM records    
)    
SELECT r.id, r.time,  -- 其他字段
       CASE   
           WHEN r.id = @CurrentID THEN 'Current'  -- @CurrentID 是当前id
           WHEN r.prev_id = @CurrentID THEN 'Previous'  
           WHEN r.next_id = @CurrentID THEN 'Next'  
       END AS record_type  
FROM RankedRecords r  
WHERE r.id = @CurrentID OR r.prev_id = @CurrentID OR r.next_id = @CurrentID  
ORDER BY CASE WHEN record_type = 'Previous' THEN 1 ELSE 2 END, r.time ASC;

代码是一言生成的，测试达到了效果，想问问数据库大佬，这段代码如果对大量数据的查询会影响性能吗，像这一行：

   SELECT *, 
           LEAD(id) OVER (ORDER BY time ASC) AS next_id,    
           LAG(id) OVER (ORDER BY time ASC) AS prev_id    
   FROM records

数据库结构：

id	time	···一些其他列
2	12345660	···
3	12345640	···
5	12345670	···

查询id=2，以time排序：

id	time	···一些其他列	record_type
3	12345640	···	Previuous
5	12345670	···	Next
2	12345660	···	Current

查询id=3：

id	time	···一些其他列	record_type
2	12345660	···	Next
3	12345640	···	Current

Answer 1

WITH RankedRecords AS (    
    SELECT id, time,  -- 只选择需要的列
           LEAD(id) OVER (ORDER BY time ASC) AS next_id,    
           LAG(id) OVER (ORDER BY time ASC) AS prev_id    
    FROM records    
)    
SELECT r.id, r.time,  -- 其他字段
       CASE   
           WHEN r.id = @CurrentID THEN 'Current'  -- @CurrentID 是当前id
           WHEN r.prev_id = @CurrentID THEN 'Previous'  
           WHEN r.next_id = @CurrentID THEN 'Next'  
       END AS record_type  
FROM RankedRecords r  
WHERE r.id = @CurrentID OR r.prev_id = @CurrentID OR r.next_id = @CurrentID  
ORDER BY CASE WHEN record_type = 'Previous' THEN 1 ELSE 2 END, r.time ASC;

Answer 2

我个人觉得,如果有传入的id 为什么不再加两条查询呢,比如下一条就是id大于传入id的值,上一条就是小于传入的id值
比如传入的3 那么 where id > 3就是下一条 where id <3就是上一条
有什么不对的可以多指教

Answer 3

time 不是唯一值，加上唯一值 ID 便于排序

SELECT *  FROM records  ORDER BY `time` ASC, `id`  ASC ;

以 2 为例， 对应的时间为 12345660

• 上一条

  有相同时间的数据

  SELECT  *  FROM records     WHERE time  = 12345660 AND id < 2  order  by `id` desc  limit 1 ;

  如果上一条没有查询出数据

  SELECT  *  FROM records     WHERE time  < 12345660  order  by `time` desc, `id`  desc limit 1 ;

• 下一条

  SELECT  *  FROM records     WHERE time  = 12345660 AND id >2  order  by `id` desc  limit 1 ;

  如果没有

  SELECT  *  FROM records     WHERE time  > 12345660 order  by `time` asc ,  `id`  ASC limit 1 ;