從多列排序的 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 對應。