如何查询同subjectId的表中指定id的上一个和下一个id

现有数据表 #

针对选中的subjectId=5且id=4的记录，这里提供两种查询同subjectId下上一个和下一个ID的方案：

方法一：子查询方式 #

这种方式兼容性强，几乎所有数据库都支持。

获取上一个ID（id小于4的最大值）

SELECT MAX(id) AS previous_id
FROM your_table_name
WHERE subjectId = 5 AND id < 4;

执行后返回结果为1。

获取下一个ID（id大于4的最小值）

SELECT MIN(id) AS next_id
FROM your_table_name
WHERE subjectId = 5 AND id > 4;

执行后返回结果为6。

一次性获取两个ID

如果想在一条查询里拿到结果，可以用：

SELECT
  (SELECT MAX(id) FROM your_table_name WHERE subjectId = 5 AND id < 4) AS previous_id,
  (SELECT MIN(id) FROM your_table_name WHERE subjectId = 5 AND id > 4) AS next_id;

方法二：窗口函数方式（适用于MySQL 8.0+、PostgreSQL等支持窗口函数的数据库） #

用LAG()和LEAD()函数可以直接定位当前记录的前后ID，写法更简洁：

SELECT
  LAG(id) OVER (PARTITION BY subjectId ORDER BY id) AS previous_id,
  LEAD(id) OVER (PARTITION BY subjectId ORDER BY id) AS next_id
FROM your_table_name
WHERE id = 4 AND subjectId = 5;

这个查询会直接返回previous_id=1、next_id=6，它通过PARTITION BY subjectId按科目分组，ORDER BY id按ID排序，分别取当前行的前一行和后一行ID。

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