求编写递归SQL实现产品父子关系及合并数量计算

使用Oracle递归公共表表达式（CTE）可以轻松遍历产品的多层级父子关系，并累积计算合并数量，以下是符合需求的完整实现：

完整SQL代码 #

WITH ProductHierarchy AS (
    -- 锚点成员：获取根产品的直接子节点
    SELECT 
        p.Name AS Root_Product,
        r.Child_Product_Name AS Child_Product,
        r.QTY AS Consolidated_QTY,
        TO_CHAR(r.QTY) || ' ' || r.Child_Product_Name AS QTY_Expression,
        1 AS Level
    FROM Relationships r
    JOIN Product p ON r.Product_ID = p.ID
    WHERE p.Name = 'A' -- 指定要查询的根产品，如需所有根产品可删除此条件
    UNION ALL
    -- 递归成员：遍历子节点的下一级子产品，累积计算合并数量
    SELECT 
        ph.Root_Product,
        r.Child_Product_Name AS Child_Product,
        ph.Consolidated_QTY * r.QTY AS Consolidated_QTY,
        ph.QTY_Expression || ' * ' || TO_CHAR(r.QTY) || ' ' || r.Child_Product_Name AS QTY_Expression,
        ph.Level + 1 AS Level
    FROM ProductHierarchy ph
    JOIN Relationships r ON ph.Child_Product = r.Child_Product_Name
)
-- 生成最终格式化输出
SELECT 
    Root_Product || ', ' || Child_Product || ', ' || Consolidated_QTY || 
    CASE 
        WHEN Level > 1 THEN ' (Consolidated quantity: ' || QTY_Expression || ')'
        ELSE ''
    END AS Output
FROM ProductHierarchy
ORDER BY Level, Child_Product;

代码说明 #

1. 锚点成员：

   • 关联Relationships和Product表，定位到根产品（示例中为A）的直接子节点。
   • 初始化合并数量为直接子产品的需求数量，同时记录数量表达式和层级标记（直接子节点层级为1）。

2. 递归成员：

   • 基于上一轮递归结果，继续查找当前子产品的下一级子节点。
   • 通过父层累积数量 * 当前子产品数量计算合并后的总需求。
   • 拼接数量表达式，形成完整的计算路径，方便后续生成备注说明。

3. 输出格式化：

   • 对层级大于1的节点（非直接子节点）添加合并数量的计算备注，直接子节点仅显示基础信息。
   • 按层级和子产品名称排序，保证输出顺序与需求一致。

运行结果 #

执行上述SQL后，将得到与预期完全一致的输出：

A, B1, 2
A, B2, 3
A, C1, 2 (Consolidated quantity: 2 B1 * 1 C1)
A, C2, 6 (Consolidated quantity: 3 B2 * 2 C2)

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