首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Postgresql获取相邻记录作为一列

问Postgresql获取相邻记录作为一列
EN

Stack Overflow用户
提问于 2021-05-22 19:59:33
回答 1查看 16关注 0票数 0

我想在Postgresql的select查询中获取相邻记录作为列

这是我的方案。

代码语言:javascript
复制
CREATE TABLE post (
  id            serial primary key,
  title         text,
  content       text
);

这是示例记录。

代码语言:javascript
复制
insert into post (title, content) values
('War and Peace snippet', 'Although Tolstoy wrote most of the book, including all the narration, in Russian, significant portions of dialogue (including its opening paragraph) are written in French with characters often switching between the two languages.'),
('War and Peace snippet', ' This reflected 19th century Russian aristocracy, where French, a foreign tongue, was widely spoken and considered a language of prestige and more refined than Russian.'),
('Anna Karenina was Tolstoy''s "first true novel"', 'Widely regarded as a pinnacle in realist fiction, Tolstoy considered Anna Karenina his first true novel, when he came to consider War and Peace to be more than a novel.'),
('War and Peace snippet', 'It has been suggested[14] that it is a deliberate literary device employed by Tolstoy, to use French to portray artifice and insincerity as the language of the theater and deceit while Russian emerges as a language of sincerity, honesty and seriousness.');

您可以使用此http://sqlfiddle.com/#!9/6e6dd6。

当我选择一个id时,我想要一个下一个和前一个记录id作为一列。

代码语言:javascript
复制
select po.id, n.id as next_id, p.id as previous_id
from post po, post n, post p
where po.id=3 and n.id> po.id and p.id < po.id limit 1;

但是它没有给我正确的结果。理想情况下,next_id应为4,previous_id应为2

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-05-22 20:01:21

你想要lead()和lag()

代码语言:javascript
复制
select p.*,
       lag(id) over (order by id) as prev_id,
       lead(id) over (order by id) as next_id
from post p;

Here是一个db<>fiddle。

注意:如果你想过滤一个特定的id,你需要使用子查询:

代码语言:javascript
复制
select p.*
from (select p.*,
             lag(id) over (order by id) as prev_id,
             lead(id) over (order by id) as next_id
      from post p
     ) p
where id = 3;
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/67649207

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档