如何根据订购数量分配确认数量并存储溢缺数据？

我来帮你搞定这个订单数量分配的需求哈！核心思路是按产品维度处理：把order_confirms里的确认数量优先分配给同产品的orders订单，分配完成后，订单没满足的部分标记为less，确认单剩余的数量标记为over，所有分配记录和溢余/不足记录都存入结果表中。

首先，咱们先创建和你预期结果结构一致的结果表：

CREATE TABLE IF NOT EXISTS order_allocation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_code VARCHAR(10),
    qty INT,
    oc_id INT,
    confirm_type ENUM('ok', 'over', 'less')
);

接下来是核心的存储过程代码，我已经帮你把逻辑捋得明明白白：

DELIMITER //

CREATE PROCEDURE allocate_order_quantities()
BEGIN
    -- 声明变量，用来控制循环和存储临时数据
    DECLARE done INT DEFAULT 0;
    DECLARE current_product VARCHAR(10);
    -- 先拿到所有涉及的产品（订单和确认单的并集）
    DECLARE cur_product CURSOR FOR 
        SELECT DISTINCT product_code FROM (
            SELECT product_code FROM orders UNION SELECT product_code FROM order_confirms
        ) AS all_products;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 清空结果表（如果需要重复执行这个存储过程的话）
    TRUNCATE TABLE order_allocation;

    -- 逐个处理每个产品
    OPEN cur_product;
    product_loop: LOOP
        FETCH cur_product INTO current_product;
        IF done THEN
            LEAVE product_loop;
        END IF;

        -- 声明当前产品的订单游标，按order_id排序保证分配顺序
        DECLARE done_order INT DEFAULT 0;
        DECLARE cur_order_id INT;
        DECLARE cur_order_qty INT;
        DECLARE remaining_order_qty INT;
        DECLARE cur_order CURSOR FOR 
            SELECT order_id, qty FROM orders WHERE product_code = current_product ORDER BY order_id;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_order = 1;

        -- 声明当前产品的确认单游标，按oc_id排序
        DECLARE done_confirm INT DEFAULT 0;
        DECLARE cur_oc_id INT;
        DECLARE cur_oc_qty INT;
        DECLARE remaining_oc_qty INT;
        DECLARE cur_confirm CURSOR FOR 
            SELECT oc_id, qty FROM order_confirms WHERE product_code = current_product ORDER BY oc_id;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_confirm = 1;

        -- 初始化确认单游标，拿到第一个确认单的信息
        OPEN cur_confirm;
        FETCH cur_confirm INTO cur_oc_id, cur_oc_qty;
        SET remaining_oc_qty = cur_oc_qty;

        -- 开始处理当前产品的每个订单
        OPEN cur_order;
        order_loop: LOOP
            FETCH cur_order INTO cur_order_id, cur_order_qty;
            IF done_order THEN
                LEAVE order_loop;
            END IF;
            SET remaining_order_qty = cur_order_qty;

            -- 用确认单的数量去填订单的需求，直到订单满足或者确认单用完
            allocate_loop: LOOP
                -- 如果确认单已经全部用完，跳出分配循环
                IF done_confirm THEN
                    LEAVE allocate_loop;
                END IF;

                -- 计算这次能分配的数量：取订单剩余需求和确认单剩余数量里的较小值
                SET @allocate_qty = LEAST(remaining_order_qty, remaining_oc_qty);

                -- 插入分配成功的记录（ok类型）
                INSERT INTO order_allocation (order_id, product_code, qty, oc_id, confirm_type)
                VALUES (cur_order_id, current_product, @allocate_qty, cur_oc_id, 'ok');

                -- 更新剩余数量
                SET remaining_order_qty = remaining_order_qty - @allocate_qty;
                SET remaining_oc_qty = remaining_oc_qty - @allocate_qty;

                -- 如果订单需求已经满足了，就去处理下一个订单
                IF remaining_order_qty = 0 THEN
                    LEAVE allocate_loop;
                END IF;

                -- 如果当前确认单用完了，就取下一个确认单
                FETCH cur_confirm INTO cur_oc_id, cur_oc_qty;
                SET remaining_oc_qty = cur_oc_qty;
            END LOOP allocate_loop;

            -- 如果订单还有没满足的需求，插入less记录
            IF remaining_order_qty > 0 THEN
                INSERT INTO order_allocation (order_id, product_code, qty, oc_id, confirm_type)
                VALUES (cur_order_id, current_product, remaining_order_qty, NULL, 'less');
            END IF;
        END LOOP order_loop;
        CLOSE cur_order;

        -- 处理剩下的确认单，插入over记录
        WHILE NOT done_confirm DO
            IF remaining_oc_qty > 0 THEN
                INSERT INTO order_allocation (order_id, product_code, qty, oc_id, confirm_type)
                VALUES (NULL, current_product, remaining_oc_qty, cur_oc_id, 'over');
            END IF;
            FETCH cur_confirm INTO cur_oc_id, cur_oc_qty;
            SET remaining_oc_qty = cur_oc_qty;
        END WHILE;
        CLOSE cur_confirm;

    END LOOP product_loop;
    CLOSE cur_product;

END //

DELIMITER ;

关键逻辑说明 #

• 全产品覆盖：通过取订单和确认单的产品并集，确保每个有订单或确认单的产品都被处理，不会遗漏。
• 顺序分配：订单按order_id排序，确认单按oc_id排序，保证分配顺序和你给出的预期结果一致。
• 精细化分配：对单个订单可能拆分分配到多个确认单，单个确认单也可能拆分分配给多个订单，完全匹配你给出的预期结果。

执行方式 #

调用存储过程后查询结果表就能看到预期的效果：

-- 调用存储过程
CALL allocate_order_quantities();

-- 查询结果
SELECT * FROM order_allocation ORDER BY id;

内容的提问来源于stack exchange，提问作者Ferenc Sz.