我想在Postgresql的select查询中获取相邻记录作为列
这是我的方案。
CREATE TABLE post (
id serial primary key,
title text,
content text
);这是示例记录。
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作为一列。
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
发布于 2021-05-22 20:01:21
你想要lead()和lag()
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,你需要使用子查询:
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;https://stackoverflow.com/questions/67649207
复制相似问题