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扫描下方二维码关注公众号和小程序↓↓↓

昵称:
随便看看
换一批
-
H2 Syntax error in SQL statement expected "identifier"阅读 1234
-
IDEA Rainbow Brackets 插件高亮显示各类括号阅读 1841
-
软考-系统架构设计师:面向对象设计阅读 1809
-
数据结构:环形链表-约瑟夫环阅读 1335
-
GoJS 自定义圆角矩形阅读 2216
-
Spring Boot 修改 RestTemplate 默认编码格式阅读 7354
-
Android build.gradle 更换资源文件路径阅读 2430
-
Spring Boot 运行工程 Lombok 报错阅读 1185
-
Linux 查找文本中重复的内容阅读 4077
-
HTML 中用 CSS 实现搜索框内添加搜索图片阅读 4963