Oracle多层父级层级查询：按层级汇总客户金额的SQL实现

需求说明 #

现有一张存储客户层级关系的表，包含客户编号、金额、祖父级编号、父级编号字段，示例数据如下：

CustomerNum     Amount  GrantParent   Parent           
-----------     ------  -----------   ------ 
8046026507      100     NULL          1872539355
8099032159      100     1872539355    8046026507
1872539355      100     NULL          NULL

需要实现SQL查询，传入指定客户编号时，汇总该客户及其所有下属层级（含直接、间接关联的父级、子级）的金额总和：

• 传入祖父级客户1872539355，返回总和300（自身+父级下属+子级下属）
• 传入父级客户8046026507，返回总和200（自身+子级下属）
• 传入子级客户8099032159，返回总和100（仅自身）

注：支持一个祖父级对应多个父级、一个父级对应多个子级的多层级场景。

解决方案：递归CTE实现 #

利用SQL的递归公共表表达式（CTE）遍历整个客户层级树，汇总目标客户及其所有下属的金额：

-- 替换YourTableName为实际表名，@TargetCustomer为传入的目标客户编号
WITH CustomerHierarchy AS (
    -- 锚点查询：选中目标客户本身
    SELECT CustomerNum, Amount
    FROM YourTableName
    WHERE CustomerNum = @TargetCustomer

    UNION ALL

    -- 递归查询：遍历所有直接/间接下属（Parent关联当前层级节点的客户）
    SELECT c.CustomerNum, c.Amount
    FROM YourTableName c
    INNER JOIN CustomerHierarchy ch 
        ON c.Parent = ch.CustomerNum
)

-- 汇总所有层级节点的金额
SELECT SUM(Amount) AS TotalAmount
FROM CustomerHierarchy
-- 若层级超过100层，添加此选项取消递归限制
OPTION (MAXRECURSION 0);

说明 #

1. 锚点成员：首先定位到传入的目标客户，作为层级树的根节点。
2. 递归成员：通过Parent字段关联，逐层遍历所有直接和间接下属客户。
3. 汇总计算：对递归CTE中所有节点的Amount求和，得到目标客户及其所有下属的总金额。
4. 递归限制：默认SQL递归层级限制为100，若业务中存在更深层级，需添加OPTION (MAXRECURSION 0)取消限制。

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