PostgreSQL 查询当前文章以及上一篇文章和下一篇文章

需求

查询出当前博客文章的上一篇和下一篇文章，显示在当前博客的尾部。

数据准备

create table if not exists article(id int, title text, status text);
insert into article values (1, '文章1', 'offline');
insert into article values (2, '文章2', 'online');
insert into article values (3, '文章3', 'offline');
insert into article values (4, '文章4', 'online');
insert into article values (5, '文章5', 'online');
insert into article values (6, '文章6', 'offline');

lag()/lead()

lag()和lead()窗口函数表示计算当前行的前n行或后n行，n默认是1。

SQL

查询 id=4 的数据

select *
from (select lead(id) over (order by id desc)    as 上一篇ID,
             lead(title) over (order by id desc) as 上一篇标题,
             id,
             title,
             lag(id) over (order by id desc)     as 下一篇ID,
             lag(title) over (order by id desc)  as 下一篇标题
      from article
      where status = 'online') temp
where id = 4;

输出

z-blog=# select *
z-blog-# from (select lead(id) over (order by id desc)    as 上一篇ID,
z-blog(#              lead(title) over (order by id desc) as 上一篇标题,
z-blog(#              id,
z-blog(#              title,
z-blog(#              lag(id) over (order by id desc)     as 下一篇ID,
z-blog(#              lag(title) over (order by id desc)  as 下一篇标题
z-blog(#       from article
z-blog(#       where status = 'online') temp
z-blog-# where id = 4;
 上一篇id | 上一篇标题 | id | title | 下一篇id | 下一篇标题
----------+------------+----+-------+----------+------------
        2 | 文章2      |  4 | 文章4 |        5 | 文章5
(1 row)

查询 id=5 的数据

select *
from (select lead(id) over (order by id desc)    as 上一篇ID,
             lead(title) over (order by id desc) as 上一篇标题,
             id,
             title,
             lag(id) over (order by id desc)     as 下一篇ID,
             lag(title) over (order by id desc)  as 下一篇标题
      from article
      where status = 'online') temp
where id = 5;

输出

z-blog=# select *
z-blog-# from (select lead(id) over (order by id desc)    as 上一篇ID,
z-blog(#              lead(title) over (order by id desc) as 上一篇标题,
z-blog(#              id,
z-blog(#              title,
z-blog(#              lag(id) over (order by id desc)     as 下一篇ID,
z-blog(#              lag(title) over (order by id desc)  as 下一篇标题
z-blog(#       from article
z-blog(#       where status = 'online') temp
z-blog-# where id = 5;
 上一篇id | 上一篇标题 | id | title | 下一篇id | 下一篇标题
----------+------------+----+-------+----------+------------
        4 | 文章4      |  5 | 文章5 |          |
(1 row)