如何用SQL连接Order与Cust_order表并按Qty分配Order_num

要实现将Order表的数量拆分分配给Cust_order表的需求，可以通过递归CTE跟踪剩余数量的方式完成，以下是适配多数主流数据库（如PostgreSQL、MySQL 8+、SQL Server）的实现代码：

WITH RECURSIVE remaining_orders AS (
    SELECT 
        Item AS item,
        Order_num,
        Qty AS remaining_qty,
        ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Order_num) AS ord
    FROM "Order"
),
remaining_customers AS (
    SELECT 
        item,
        cust_ord,
        qty AS remaining_qty,
        ROW_NUMBER() OVER (PARTITION BY item ORDER BY cust_ord) AS cust_seq
    FROM Cust_order
),
allocation AS (
    -- 初始分配：第一个客户订单匹配第一个Order
    SELECT 
        rc.item,
        rc.cust_ord,
        LEAST(rc.remaining_qty, ro.remaining_qty) AS allocated_qty,
        ro.Order_num,
        ro.remaining_qty - LEAST(rc.remaining_qty, ro.remaining_qty) AS order_remaining,
        rc.remaining_qty - LEAST(rc.remaining_qty, ro.remaining_qty) AS cust_remaining,
        rc.cust_seq,
        ro.ord AS order_ord
    FROM remaining_customers rc
    JOIN remaining_orders ro ON rc.item = ro.item AND rc.cust_seq = 1 AND ro.ord = 1

    UNION ALL

    -- 递归处理剩余需求与订单
    SELECT 
        a.item,
        CASE WHEN a.cust_remaining > 0 THEN a.cust_ord ELSE rc_next.cust_ord END,
        LEAST(
            CASE WHEN a.cust_remaining > 0 THEN a.cust_remaining ELSE rc_next.remaining_qty END,
            CASE WHEN a.order_remaining > 0 THEN a.order_remaining ELSE ro_next.remaining_qty END
        ) AS allocated_qty,
        CASE WHEN a.order_remaining > 0 THEN a.Order_num ELSE ro_next.Order_num END,
        CASE 
            WHEN a.order_remaining > 0 THEN a.order_remaining - LEAST(a.cust_remaining, a.order_remaining)
            ELSE ro_next.remaining_qty - LEAST(rc_next.remaining_qty, ro_next.remaining_qty)
        END AS order_remaining,
        CASE 
            WHEN a.cust_remaining > 0 THEN a.cust_remaining - LEAST(a.cust_remaining, a.order_remaining)
            ELSE rc_next.remaining_qty - LEAST(rc_next.remaining_qty, ro_next.remaining_qty)
        END AS cust_remaining,
        CASE WHEN a.cust_remaining > 0 THEN a.cust_seq ELSE rc_next.cust_seq END,
        CASE WHEN a.order_remaining > 0 THEN a.order_ord ELSE ro_next.ord END
    FROM allocation a
    LEFT JOIN remaining_customers rc_next ON a.item = rc_next.item AND rc_next.cust_seq = a.cust_seq + 1 AND a.cust_remaining = 0
    LEFT JOIN remaining_orders ro_next ON a.item = ro_next.item AND ro_next.ord = a.order_ord + 1 AND a.order_remaining = 0
    WHERE a.cust_remaining > 0 OR a.order_remaining > 0 OR rc_next.cust_seq IS NOT NULL
)
-- 聚合最终分配结果
SELECT 
    item,
    cust_ord,
    SUM(allocated_qty) AS qty,
    Order_num
FROM allocation
GROUP BY item, cust_ord, Order_num
ORDER BY cust_ord;

逻辑说明 #

1. 剩余量初始化：通过remaining_orders和remaining_customers给订单、客户订单编号，确定处理顺序并记录初始剩余数量
2. 递归分配：从第一个客户订单和第一个Order开始，每次分配两者中较小的可用数量，更新剩余量；若当前订单耗尽则切换下一个Order，若当前客户需求满足则切换下一个客户订单
3. 结果聚合：将递归过程中的多次分配记录合并，得到每个客户订单对应的最终分配结果

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