MySQL查询当前数据的上一条，下一条？-灵析社区

走你啊啊啊啊啊

获取当前数据的上一条，下一条的 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

阅读量：209

点赞量：0

问AI
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;