如何在指定排序规则的笔记表中查询某ID条目的上下相邻记录

首先明确查询前提：笔记展示的排序规则为 pinned_flag 降序（置顶内容在前）、note_date 降序（新内容在前），以下为两种常用实现方案：

方案1：支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等） #

通过 ROW_NUMBER() 窗口函数先按排序规则给所有记录生成排名，再定位目标ID的排名，取排名±1的记录即可。

WITH ranked_notes AS (
    SELECT 
        *,
        -- 按展示规则生成排名
        ROW_NUMBER() OVER (ORDER BY pinned_flag DESC, note_date DESC) AS rank_num
    FROM notes
)
SELECT id, note_text, note_date, pinned_flag
FROM ranked_notes
-- 取目标ID的上一条（排名-1）和下一条（排名+1）
WHERE rank_num = (SELECT rank_num FROM ranked_notes WHERE id = 5) - 1
   OR rank_num = (SELECT rank_num FROM ranked_notes WHERE id = 5) + 1;

将语句中的 id = 5 替换为你要查询的目标ID即可，若目标ID是第一条记录只会返回下一条，是最后一条记录只会返回上一条。

方案2：兼容无窗口函数的旧版本数据库（如MySQL 5.x） #

通过两条独立查询分别获取上下相邻记录，再用 UNION 合并结果：

-- 查询上一条相邻记录
(SELECT id, note_text, note_date, pinned_flag
 FROM notes
 WHERE (pinned_flag > (SELECT pinned_flag FROM notes WHERE id = 5))
    OR (pinned_flag = (SELECT pinned_flag FROM notes WHERE id = 5) AND note_date > (SELECT note_date FROM notes WHERE id = 5))
 ORDER BY pinned_flag DESC, note_date DESC
 LIMIT 1)
UNION
-- 查询下一条相邻记录
(SELECT id, note_text, note_date, pinned_flag
 FROM notes
 WHERE (pinned_flag < (SELECT pinned_flag FROM notes WHERE id = 5))
    OR (pinned_flag = (SELECT pinned_flag FROM notes WHERE id = 5) AND note_date < (SELECT note_date FROM notes WHERE id = 5))
 ORDER BY pinned_flag DESC, note_date DESC
 LIMIT 1);

注意事项 #

• 如果 note_date 以字符串格式存储，需要替换为对应数据库的日期转换函数进行比较，例如MySQL中使用 STR_TO_DATE(note_date, '%d/%m/%Y') 替换语句中的note_date，避免字符串排序出现逻辑错误。
• 若存在多条pinned_flag和note_date完全相同的记录，可以在排序规则最后补充 id DESC 避免排名重复问题。

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