代码之家 › 专栏 › 技术社区 › Alpha Geek

Postgresql获取相邻记录作为列

postgresql database sql

Alpha Geek · 技术社区 · 4 年前

我想在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

1 回复 | 直到 4 年前

Gordon Linoff 4 年前

你想要什么 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<&燃气轮机;小提琴。

注意:如果要筛选特定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;

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

1 年前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

1 年前

John D · 需要为NULL或NOT NULL的WHERE子句

1 年前

ojek · 如何对SQL结果进行分组和编号?

1 年前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

1 年前

Sax · 规范化Google表格(第一步)

1 年前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

1 年前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

1 年前

Andrus · 如何在sql中查找第二个匹配项

1 年前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

1 年前