MySQL查询列调用存储过程报错及订单部门负载计算需求

场景说明 #

• 拥有Orders表与Components表，二者为多对多关系，关联表order_component包含quantity字段
• 已实现calculateRecursiveComponentLoad存储过程，可递归计算指定identifier的组件在所有部门的负载，运行正常
• 需要实现CalculateOrderLoad存储过程，用于计算指定订单在各部门的负载并返回明细数据，但当前尝试的代码报错calculateRecursiveComponentLoad不存在，且查询逻辑无法返回部门负载明细

现有递归组件负载存储过程 #

CREATE PROCEDURE calculateRecursiveComponentLoad(IN identifierie VARCHAR(50))
BEGIN
    WITH RECURSIVE components_recursive AS (
        SELECT id, identifier, 1 AS quantity
        FROM components
        WHERE identifier = identifierie
        UNION ALL
        SELECT c.id, c.identifier, cr.quantity * cr2.quantity
        FROM components c
        INNER JOIN component_recursive_component cr ON cr.component_id = c.id
        INNER JOIN components_recursive cr2 ON cr2.id = cr.parent_id
    )
    SELECT s.description, COALESCE(SUM(o.operation_time * cr.quantity), 0) AS total_time
    FROM sections s
    LEFT JOIN operations o ON o.sections_id = s.id
    LEFT JOIN processes p ON p.operations_id = o.id
    LEFT JOIN components_recursive cr ON cr.id = p.components_id
    GROUP BY s.description;
END

注：原CTE存在字段数不匹配问题，已修正（递归部分统一为3个字段：id、identifier、quantity，且递归时计算嵌套组件的总数量）

原订单负载计算尝试代码（报错版本） #

CREATE PROCEDURE CalculateOrderLoad(IN orderNum INT)
BEGIN
    DECLARE total_load DECIMAL(10, 2);

    SELECT SUM(load * quantity) INTO load_total
    FROM (
        SELECT c.id, c.identifier, pc.quantity, calculateRecursiveComponentLoad(c.identifier) AS load
        FROM orders p
        JOIN order_component pc ON p.id = pc.order_id
        JOIN components c ON pc.component_id = c.id
        WHERE p.order_num = orderNum
    ) AS components;

    SELECT total_load;
END;

错误原因 #

1. 存储过程无法作为标量调用：calculateRecursiveComponentLoad返回的是多行多列的结果集，不能像标量函数那样直接在SELECT字段中调用，这是报错的核心原因
2. 逻辑无法返回部门明细：原代码试图计算总负载，无法按部门拆分展示明细

修正后的订单负载存储过程 #

CREATE PROCEDURE CalculateOrderLoad(IN orderNum INT)
BEGIN
    WITH RECURSIVE order_components AS (
        -- 获取订单关联的顶层组件及其数量
        SELECT c.id, c.identifier, pc.quantity AS order_quantity
        FROM orders p
        JOIN order_component pc ON p.id = pc.order_id
        JOIN components c ON pc.component_id = c.id
        WHERE p.order_num = orderNum
    ),
    components_recursive AS (
        -- 递归展开所有组件（包括子组件），计算每个组件的总数量（订单数量 × 嵌套数量）
        SELECT oc.id, oc.identifier, oc.order_quantity AS total_quantity
        FROM order_components oc
        UNION ALL
        SELECT c.id, c.identifier, cr.quantity * cr2.total_quantity
        FROM components c
        INNER JOIN component_recursive_component cr ON cr.component_id = c.id
        INNER JOIN components_recursive cr2 ON cr2.id = cr.parent_id
    )
    -- 按部门汇总负载
    SELECT s.description, COALESCE(SUM(o.operation_time * cr.total_quantity), 0) AS department_load
    FROM sections s
    LEFT JOIN operations o ON o.sections_id = s.id
    LEFT JOIN processes p ON p.operations_id = o.id
    LEFT JOIN components_recursive cr ON cr.id = p.components_id
    GROUP BY s.description
    ORDER BY s.description;
END;

说明 #

• 整合递归逻辑到订单负载存储过程中，先获取订单关联的顶层组件，再递归展开所有子组件，同时计算每个组件的总数量（订单中该组件的数量 × 嵌套层级的数量）
• 最后关联部门、工序、流程表，按部门汇总计算总负载，直接返回各部门的负载明细
• 解决了原代码中无法调用存储过程的问题，同时满足返回部门负载明细的需求

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