Java OpenResty Spring Spring Boot MySQL Redis MongoDB PostgreSQL Linux Android Nginx 面试 小程序 Arthas JVM AQS juc Kubernetes Docker 诊断工具


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

PostgreSQL 大约 2392 字

需求

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

数据准备

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)
阅读 87 · 发布于 2023-11-15

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb

扫描下方二维码关注公众号和小程序↓↓↓

扫描二维码关注我
昵称:
随便看看 换一批