从按多列排序的 MySQL 查询中检索下一条/上一条记录

Question

我正在通过我的 MySQL 数据库 (MariaDB 10.3) 运行查询，如下所示：

SELECT * FROM my_table ORDER BY priority DESC, expiration_date ASC, id ASC

具有给定顺序的该表的示例如下所示：

id	...	优先	expiration_date
3	...	2	2022-07-01 12:00:00
7	...	2	2022-07-03 12:00:00
6	...	2	2022-07-04 12:00:00
9	...	1	2022-07-02 12:00:00
4	...	1	2022-07-05 12:00:00
11	...	1	2022-07-05 12:00:00

现在我已经有了特定记录的 ID，并且我正在尝试通过 SQL 给定的顺序检索查询结果中位于所述记录之前/之后的记录。假设我有记录 ID 6，我想分别返回 ID 为 9 和 7 的记录。

通过单个唯一列进行排序，这在单个查询中很容易获得，但我不确定如何处理多个非唯一列。有人可以告诉我如何实现这一目标吗？

Answer 1

根据 Paul Maxwell 关于 LEAD 和 LAG 的提示，我能够编写如下工作查询：

SELECT t.next_id 
FROM (
    SELECT id, 
        LEAD(id, 1) OVER (ORDER BY priority DESC, expiration_date ASC, id ASC) AS next_id 
    FROM my_table
) t 
WHERE t.id = ?

检索以前的记录与 LAG 相应。