如何按Invoice Id和PO[0-9]行分组生成InvoiceLineItem行号？

数据老陈

2026-6-22

动态生成InvoiceLineItem行号的正确实现方案

你的原有代码分别对非PO行和PO行独立生成行号后合并，这种方式仅在两类行数量完全匹配时能得到正确结果，一旦数量不匹配，行号就会错位。以下是符合分组规则的正确实现：

WITH AllLines AS (
    SELECT 
        Invoice,
        PurchaseOrderNumber,
        Quantity,
        UnitPrice,
        LineItemTotal,
        -- 标记当前行是否为非PO行
        CASE WHEN PurchaseOrderNumber NOT LIKE 'PO%' THEN 1 ELSE 0 END AS IsNonPO
    FROM InvoiceLineItems
    WHERE Template = 'cw_engineering'
),
NumberedLines AS (
    SELECT 
        *,
        -- 按Invoice分区，累计非PO行数量生成行号
        SUM(IsNonPO) OVER (
            PARTITION BY Invoice 
            ORDER BY PurchaseOrderNumber 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS RN
    FROM AllLines
)
SELECT 
    Invoice,
    PurchaseOrderNumber,
    Quantity,
    UnitPrice,
    LineItemTotal,
    RN
FROM NumberedLines
ORDER BY Invoice, RN, PurchaseOrderNumber;

逻辑说明

筛选并标记行类型：通过AllLines CTE筛选出cw_engineering模板的所有行，同时用IsNonPO标记每行是否为非PO行；
生成分组行号：在NumberedLines中，使用窗口聚合函数SUM(IsNonPO)，按Invoice分区，以PurchaseOrderNumber排序（可替换为你业务需要的排序字段），累计当前行及之前所有行的非PO标记值。这样每个非PO行会触发行号递增，后续的PO行会继承当前行号，直到下一个非PO行出现；
排序输出：最终按Invoice、行号RN、PurchaseOrderNumber排序，确保分组结果符合预期。