postgresql - 从项目中查找下一行和前一行

Question

在一个网络应用程序中，我有一个项目轮播。

<Prev | Item 1 | Item 2 (active) |  Item 3 | Next>

当用户直接访问应预选“项目 2”的页面时 - 我需要前一项和下一项。

所以查询应该类似于“给我第 2 项之前和第 2 项之后的行，并且只有这两个。”

如果用户可以访问该项目，则给定以下带有 id 和 bool 字段的虚拟数据:

CREATE TABLE items (id integer, can_access boolean);

INSERT INTO items SELECT 1,true;
INSERT INTO items SELECT 2,false;
INSERT INTO items SELECT 3,true;
INSERT INTO items SELECT 5,false;
INSERT INTO items SELECT 6,true;

当 id:3 是事件 id - 我想找到 id:1 和 id:6。

Answer 1

使用窗口函数获取所有页面的相对导航，您的查询将如下所示:

SELECT
    lag("id") over w AS prev,
    id,
    lead("id") over w AS next
FROM items
WHERE can_access = true
WINDOW w as (order by id) 

给你这些结果:

prev    active    next
-----------------------
NULL    1         3
1       3         6
3       6         NULL

要仅返回 3 的导航，您可以使用中级 CTE:

WITH 
    navi(prev, active, "next") AS
    (
        SELECT
            lag("id") over w AS prev,
            id,
            lead("id") over w AS next
        FROM items
        WHERE can_access = true
        WINDOW w as (order by id) 
    )
SELECT * FROM navi WHERE active = 3