如何递归查询Budget Line对应最末级Expense并过滤中间层级数据

解决方案 #

核心逻辑说明

最末级Expense的判定规则为：不存在任何其他Expense记录将当前Expense作为父节点（即ParentId = 当前Expense.Id且ParentType = 'Expense'）。在原有递归追溯BudgetLine的逻辑基础上，新增最末级节点过滤即可。

完整可运行查询代码

WITH RCTE AS
(
    -- 锚点：每个Expense本身，记录起始ID和基础信息
    SELECT 
        e.Id, 
        e.ParentId, 
        e.ParentType, 
        e.Description,
        1 AS Lvl, 
        e.Id as StartExpenseId 
    FROM Expense e
    UNION ALL
    -- 递归向上追溯父节点，直到父节点类型为BudgetLine
    SELECT 
        rh.Id, 
        rh.ParentId, 
        rh.ParentType,
        rh.Description,
        Lvl+1 AS Lvl, 
        rc.StartExpenseId 
    FROM dbo.Expense rh
    INNER JOIN RCTE rc ON rh.Id = rc.ParentId and rc.ParentType = 'Expense'
),
-- 关联每个Expense和所属的顶层BudgetLine
ExpenseToBudgetLine AS
(
    SELECT 
        StartExpenseId AS ExpenseId, 
        ParentId AS BudgetLineId,
        (SELECT Description FROM Expense WHERE Id = StartExpenseId) AS ExpenseDescription
    FROM RCTE 
    WHERE ParentType = 'BudgetLine'
),
-- 过滤所有最末级Expense节点
LeafExpense AS
(
    SELECT Id 
    FROM Expense e
    WHERE NOT EXISTS (
        SELECT 1 FROM Expense sub 
        WHERE sub.ParentId = e.Id AND sub.ParentType = 'Expense'
    )
)
-- 输出最终结果
SELECT 
    etbl.BudgetLineId,
    etbl.ExpenseId,
    etbl.ExpenseDescription AS Description
FROM ExpenseToBudgetLine etbl
INNER JOIN LeafExpense le ON etbl.ExpenseId = le.Id
ORDER BY etbl.BudgetLineId, etbl.ExpenseId

执行结果验证

针对示例数据运行上述代码，输出结果和期望完全一致：

内容的提问来源于stack exchange，提问作者S. Walker