如何高效为分层结构的 ledger 表生成Group_Id交易分组标识？

直接用递归CTE就能实现，不需要游标和临时表，完全基于集合操作，性能更适配几十万条量级的数据集。核心思路是：遍历每个层级节点，找到其所属交易集的顶层根节点（即Closed_By_Entry_No=0的记录），用根节点的Entry_No作为整个交易集的Group_Id，确保同一交易链上的所有记录共享同一个Group_Id。

具体SQL实现（以SQL Server为例） #

CREATE VIEW Cleaned_Ledger AS
WITH Recursive_Ledger AS (
    -- 锚点成员：所有顶层交易记录（根节点）
    SELECT
        Entry_No,
        Closed_By_Entry_No,
        -- 根节点的Group_Id就是自身的Entry_No
        Entry_No AS Group_Id,
        -- 记录层级，可选，用于调试
        1 AS Level
    FROM Ledger
    WHERE Closed_By_Entry_No = 0

    UNION ALL

    -- 递归成员：关联子节点，继承父节点的Group_Id
    SELECT
        l.Entry_No,
        l.Closed_By_Entry_No,
        r.Group_Id,
        r.Level + 1 AS Level
    FROM Ledger l
    INNER JOIN Recursive_Ledger r ON l.Closed_By_Entry_No = r.Entry_No
)
SELECT
    -- 输出原表所有字段 + 新增的Group_Id
    l.*,
    r.Group_Id
FROM Ledger l
LEFT JOIN Recursive_Ledger r ON l.Entry_No = r.Entry_No;

关键说明 #

1. 递归逻辑：锚点成员先定位所有顶层交易，递归成员通过Closed_By_Entry_No关联父节点，直接继承父节点的Group_Id——不管层级有多深，最终所有子节点都会追溯到顶层根节点的Entry_No作为Group_Id。
2. 性能优化：
   • 给Ledger表的Entry_No（主键）和Closed_By_Entry_No建联合索引或单独索引，递归关联时能大幅减少查询时间。
   • 如果是PostgreSQL等数据库，改用WITH RECURSIVE语法，逻辑完全一致。
3. 复杂场景适配：不管是1发票+1付款的简单组，还是多发票+多付款+贷项通知单的复杂组合，只要层级关联正确，所有关联记录都会被归到同一个Group_Id下。

示例效果 #

假设原表有以下数据：

清洗后视图的Group_Id列会是：

内容的提问来源于stack exchange，提问作者maclura