Oracle递归SQL查询需求：关联订单统计与金额求和

需求说明 #

1. 关联零件订单统计 #

• 搜索零件1：统计包含零件1及关联零件（如零件2）的所有唯一订单，预期结果3（订单A、B、C）
• 搜索零件2：统计包含零件2及关联零件（如零件1）的所有唯一订单，预期结果3（订单A、B、C）
• 搜索零件3：仅统计包含零件3的订单，预期结果2（订单D、E）
• 搜索零件4：仅统计包含零件4的订单，预期结果1（订单F）

2. 订单最大金额求和 #

• 搜索零件1：对符合条件的订单取各自最大AMOUNT后求和，预期结果45（10+15+20）
• 搜索零件2：同上述逻辑，预期结果45（10+15+20）
• 搜索零件3：预期结果40（30+10）
• 搜索零件4：预期结果10（10）

数据结构 #

CREATE TABLE ORDER_PARTS (
    ORDER_NO VARCHAR2(10),
    PART_NO NUMBER,
    AMOUNT NUMBER
);

INSERT INTO ORDER_PARTS VALUES ('A', 1, 10);
INSERT INTO ORDER_PARTS VALUES ('B', 1, 10);
INSERT INTO ORDER_PARTS VALUES ('B', 1, 15);
INSERT INTO ORDER_PARTS VALUES ('B', 2, 10);
INSERT INTO ORDER_PARTS VALUES ('C', 2, 20);
INSERT INTO ORDER_PARTS VALUES ('D', 3, 30);
INSERT INTO ORDER_PARTS VALUES ('E', 3, 10);
INSERT INTO ORDER_PARTS VALUES ('F', 4, 10);
COMMIT;

解决方案SQL #

通用查询（替换&SEARCH_PART为目标零件号） #

WITH PART_RELATIONS AS (
    -- 建立零件双向关联：同订单的零件互为关联
    SELECT DISTINCT
        p1.PART_NO AS SOURCE_PART,
        p2.PART_NO AS RELATED_PART
    FROM ORDER_PARTS p1
    JOIN ORDER_PARTS p2 ON p1.ORDER_NO = p2.ORDER_NO
    WHERE p1.PART_NO != p2.PART_NO
),
RECURSIVE_PARTS AS (
    -- 递归遍历所有关联零件
    SELECT SOURCE_PART AS PART_NO
    FROM PART_RELATIONS
    WHERE SOURCE_PART = &SEARCH_PART
    UNION ALL
    SELECT pr.RELATED_PART
    FROM RECURSIVE_PARTS rp
    JOIN PART_RELATIONS pr ON rp.PART_NO = pr.SOURCE_PART
    WHERE pr.RELATED_PART NOT IN (SELECT PART_NO FROM RECURSIVE_PARTS)
),
ALL_RELEVANT_PARTS AS (
    -- 合并初始零件与所有关联零件
    SELECT &SEARCH_PART AS PART_NO FROM DUAL
    UNION
    SELECT PART_NO FROM RECURSIVE_PARTS
),
ORDER_MAX_AMOUNT AS (
    -- 预计算每个订单的最大金额
    SELECT ORDER_NO, MAX(AMOUNT) AS MAX_AMOUNT
    FROM ORDER_PARTS
    GROUP BY ORDER_NO
)
-- 最终统计结果
SELECT
    COUNT(DISTINCT op.ORDER_NO) AS ORDER_COUNT,
    SUM(oma.MAX_AMOUNT) AS TOTAL_MAX_AMOUNT
FROM ORDER_PARTS op
JOIN ALL_RELEVANT_PARTS arp ON op.PART_NO = arp.PART_NO
JOIN ORDER_MAX_AMOUNT oma ON op.ORDER_NO = oma.ORDER_NO
GROUP BY NULL;

测试结果示例 #

• 替换&SEARCH_PART为1：返回ORDER_COUNT=3，TOTAL_MAX_AMOUNT=45
• 替换&SEARCH_PART为2：返回ORDER_COUNT=3，TOTAL_MAX_AMOUNT=45
• 替换&SEARCH_PART为3：返回ORDER_COUNT=2，TOTAL_MAX_AMOUNT=40
• 替换&SEARCH_PART为4：返回ORDER_COUNT=1，TOTAL_MAX_AMOUNT=10

逻辑说明 #

1. PART_RELATIONS：通过订单关联提取所有有共同订单的零件对，构建双向关联关系
2. RECURSIVE_PARTS：递归遍历，找出初始零件的所有间接关联零件
3. ALL_RELEVANT_PARTS：合并初始零件与关联零件，得到完整的目标零件范围
4. ORDER_MAX_AMOUNT：预先计算每个订单的最大金额，避免重复计算
5. 最终关联所有表，统计符合条件的唯一订单数，并求和各订单的最大金额

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