如何将当前行与PostgreSQL中的下一行和上一行进行比较？

SELECT tokcat.text
FROM (
    SELECT text,category,chartype,lag(category,1) OVER w as previousCategory,lead(category,1) OVER w as nextCategory
    FROM token t,textBlockHasToken tb
    WHERE tb.tokenId = t.id
    WINDOW w AS (
        PARTITION BY textBlockId,sentence
        ORDER BY textBlockId,sentence,position
    )
) tokcat
WHERE 'NAME' = ANY(previousCategory)
AND 'NAME' = ANY(nextCategory)
AND 'NAME' <> ANY(category)

简体版本：

SELECT text
FROM (
    SELECT text,lag(category) OVER w as previous_cat,lead(category) OVER w as next_cat
    FROM   token t
    JOIN   textblockhastoken tb ON tb.tokenid = t.id
    WINDOW w AS (PARTITION BY textblockid,sentence ORDER BY position)
    ) tokcat
WHERE  category <> 'NAME'
AND    previous_cat = 'NAME'
AND    next_cat = 'NAME';

要点

> = ANY()不需要,窗口函数返回单个值>子查询中的一些冗余字段>不需要按列进行排序,即PARTITION BY – ORDER BY适用于分区>不要使用混合的案例标识符,而不引用,它只会导致混淆. (更好的是：不要在PostgreSQL中使用混合大小写标识符)