SQL递归查询实现人员层级销售逐级分成计算问题排查

错误原因 #

你的递归CTE存在两个核心问题：

• 字段引用错误：Sales表的金额字段为Sale，原代码错误写为Price会直接抛出字段不存在的报错。
• 逻辑设计错误：仅用单个字段同时存储「当前人员分成」和「向上传递的剩余金额」，两个数值混淆计算导致结果不符合规则。

修改后正确实现 #

我们需要先从有销售额的底层人员递归向上查询所有上级，记录每个节点相对于底层的层级深度，再通过系数计算每个人的对应分成：

WITH cte_persons AS (
    -- 锚点：从有销售额的最底层人员开始
    SELECT 
        p.Id,
        p.ParentId,
        p.Name,
        s.Sale AS total_sale,
        1 AS depth -- 底层人员层级标记为1
    FROM Persons p
    INNER JOIN Sales s ON s.PersonId = p.Id
    UNION ALL
    -- 递归向上查询所有上级，每上一级层级+1
    SELECT 
        p.Id,
        p.ParentId,
        p.Name,
        c.total_sale,
        c.depth + 1 AS depth
    FROM Persons p
    INNER JOIN cte_persons c ON c.ParentId = p.Id
),
cte_max_depth AS (
    -- 统计整根链路的总层级
    SELECT MAX(depth) AS max_d FROM cte_persons
)
SELECT 
    c.Id,
    c.ParentId,
    c.Name,
    CAST(
        CASE WHEN c.depth = 1 
            -- 底层人员拿扣完所有上级分成后的剩余金额
            THEN c.total_sale * POWER(0.8, m.max_d - 1)
            -- 各级上级拿对应传递金额的20%
            ELSE c.total_sale * 0.2 * POWER(0.8, m.max_d - c.depth)
        END AS DECIMAL(6,2)
    ) AS Sale
FROM cte_persons c
CROSS JOIN cte_max_depth m
ORDER BY c.Id;

针对你提供的测试数据，上述代码运行后输出结果和预期完全一致。

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