Postgres如何基于给定发票ID查询同项目对应的上一张发票ID

SQL的执行顺序中，WHERE子句的执行优先级高于窗口函数。你原有的查询先通过WHERE id = 2将结果集过滤为仅1行数据，后续LAG窗口函数只能在这1行的范围内计算，自然无法获取到同项目下的上一张发票ID。
另外你原有的窗口函数未添加项目分区逻辑，即便没有过滤条件，也会对全表所有发票按ID排序取前值，而非仅在发票所属的项目范围内取值，不符合业务需求。

你可以先通过子查询/CTE预先计算好所有发票对应的同项目上一张发票ID，再外层过滤你需要查询的指定发票ID即可，不需要手动遍历全量结果，数据库会自动优化查询效率。

子查询写法（兼容所有支持窗口函数的数据库） #

SELECT id, previous_invoice_id
FROM (
    SELECT
        id,
        LAG(id) OVER (
            PARTITION BY project_id -- 按项目分区，仅在同项目范围内计算前序发票
            ORDER BY id -- 同项目内按发票ID升序排序，可替换为开票日期等你需要的排序字段
        ) AS previous_invoice_id
    FROM invoice
) t
WHERE id = 2; -- 外层过滤你需要查询的指定发票ID

CTE写法（逻辑和子查询一致，可读性更高） #

WITH invoice_with_prev AS (
    SELECT
        id,
        LAG(id) OVER (PARTITION BY project_id ORDER BY id) AS previous_invoice_id
    FROM invoice
)
SELECT id, previous_invoice_id
FROM invoice_with_prev
WHERE id = 2;

如果指定发票是所属项目的第一张发票，查询返回的previous_invoice_id会为NULL，符合预期逻辑。

内容的提问来源于stack exchange，提问作者Mirza Suljić