使用PostgreSQL进行当前行前后数据检索

一、背景

在查看文章时，想做一个点击查看下一篇或者上一篇文章的功能，当时没写过类似的功能/SQL，所以这次记录下实现这个功能的过程

二、概述

在实现该功能时，最核心的两个函数是：

• LAG(): 用于获取当前行之前的某一行数据（即“上一篇”）。
• LEAD(): 用于获取当前行之后的某一行数据（即“下一篇”）。
  这两个函数属于窗口函数，它们可以在不改变原始结果集行数的情况下，为每一行附带上相邻行的数据。

三、函数语法

1. LAG（获取上一条）

LAG(column_name, offset, default_value) OVER (ORDER BY sorting_column)

2. LEAD（获取下一条）

LEAD(column_name, offset, default_value) OVER (ORDER BY sorting_column)

参数说明：

• column_name: 你想要获取的字段名（如 id 或 title）。
• offset: 偏移量。默认是 1，表示上一条/下一条。
• default_value: 如果找不到（比如第一篇没有上一篇），返回的默认值。
• OVER (ORDER BY ...): 必须指定，用来定义“前后”的逻辑顺序（通常是发布时间或 ID）。

实际使用的SQL

SELECT * FROM (
	SELECT 
		LAG(id) OVER (ORDER BY pinned DESC, published DESC, id DESC)     AS prev_id,
        LAG(title) OVER (ORDER BY pinned DESC, published DESC, id DESC)  AS prev_title,
        id                                                               as current_id,
        title                                                            as current_title,
        LEAD(id) OVER (ORDER BY pinned DESC, published DESC, id DESC)    AS next_id,
        LEAD(title) OVER (ORDER BY pinned DESC, published DESC, id DESC) AS next_title
    FROM blog_detail
    WHERE draft = false
        AND is_deleted = false
) TEMP
WHERE TEMP.current_id = :blog_id; -- 替换为你要查询的 ID