如何实现订单仅一次匹配发货并排除后续重复选中？

针对订单需求量远超供应量、高优先级订单易被多发货单重复选中的问题，可通过递归CTE实现无临时表的实时分配，确保每个订单仅被匹配一次，同时严格遵循优先级规则。

完整实现SQL #

WITH SHIPMENTS AS (
     SELECT 'A' SHIPMENT, 10 QUANTITY FROM dual UNION ALL
     SELECT 'B' SHIPMENT, 10 QUANTITY FROM dual UNION ALL
     SELECT 'C' SHIPMENT, 20 QUANTITY FROM dual
     ),
     
     ORDERS AS (
     SELECT '1' ORDERID, 5 QUANTITY FROM dual UNION ALL
     SELECT '2' ORDERID, 5 QUANTITY FROM dual UNION ALL
     SELECT '3' ORDERID, 10 QUANTITY FROM dual UNION ALL
     SELECT '4' ORDERID, 20 QUANTITY FROM dual UNION ALL
     SELECT '5' ORDERID, 20 QUANTITY FROM dual
     ),
     
     ORDER_SHIPMENT_RANK AS (
     SELECT 'A' SHIPMENT, '1' ORDERID, 10 SHIP_QTY, 5 ORDER_QTY, 1000 RANK FROM dual UNION ALL
     SELECT 'A' SHIPMENT, '2' ORDERID, 10 SHIP_QTY, 5 ORDER_QTY, 900 RANK FROM dual UNION ALL
     SELECT 'A' SHIPMENT, '3' ORDERID, 10 SHIP_QTY, 10 ORDER_QTY, 500 RANK FROM dual UNION ALL
     SELECT 'A' SHIPMENT, '4' ORDERID, 10 SHIP_QTY, 20 ORDER_QTY, 400 RANK FROM dual UNION ALL
     SELECT 'A' SHIPMENT, '5' ORDERID, 10 SHIP_QTY, 20 ORDER_QTY, 100 RANK FROM dual UNION ALL
     SELECT 'B' SHIPMENT, '1' ORDERID, 10 SHIP_QTY, 5 ORDER_QTY, 1200 RANK FROM dual UNION ALL
     SELECT 'B' SHIPMENT, '2' ORDERID, 10 SHIP_QTY, 5 ORDER_QTY, 1100 RANK FROM dual UNION ALL
     SELECT 'B' SHIPMENT, '3' ORDERID, 10 SHIP_QTY, 10 ORDER_QTY, 800 RANK FROM dual UNION ALL
     SELECT 'B' SHIPMENT, '4' ORDERID, 10 SHIP_QTY, 20 ORDER_QTY, 500 RANK FROM dual UNION ALL
     SELECT 'B' SHIPMENT, '5' ORDERID, 10 SHIP_QTY, 20 ORDER_QTY, 200 RANK FROM dual UNION ALL
     SELECT 'C' SHIPMENT, '1' ORDERID, 20 SHIP_QTY, 5 ORDER_QTY, 1500 RANK FROM dual UNION ALL
     SELECT 'C' SHIPMENT, '2' ORDERID, 20 SHIP_QTY, 5 ORDER_QTY, 1300 RANK FROM dual UNION ALL
     SELECT 'C' SHIPMENT, '3' ORDERID, 20 SHIP_QTY, 10 ORDER_QTY, 1000 RANK FROM dual UNION ALL
     SELECT 'C' SHIPMENT, '5' ORDERID, 20 SHIP_QTY, 20 ORDER_QTY, 900 RANK FROM dual UNION ALL
     SELECT 'C' SHIPMENT, '4' ORDERID, 20 SHIP_QTY, 20 ORDER_QTY, 600 RANK FROM dual 
     ),
     -- 为每个发货单内的订单对按优先级降序编号
     ranked_pairs AS (
         SELECT 
             sr.*,
             ROW_NUMBER() OVER (PARTITION BY sr.SHIPMENT ORDER BY sr.RANK DESC) AS pair_seq
         FROM ORDER_SHIPMENT_RANK sr
     ),
     -- 递归CTE：跟踪已分配订单、发货单剩余量
     allocation AS (
         -- 初始步骤：处理第一个发货单的最高优先级订单
         SELECT 
             rp.SHIPMENT,
             rp.ORDERID,
             rp.ORDER_QTY AS ALLOCATED_QTY,
             rp.SHIP_QTY - rp.ORDER_QTY AS REMAINING_SHIP_QTY,
             CAST(rp.ORDERID AS VARCHAR2(1000)) AS ALLOCATED_ORDERS,
             rp.pair_seq AS current_pair_seq
         FROM ranked_pairs rp
         WHERE rp.SHIPMENT = (SELECT MIN(SHIPMENT) FROM SHIPMENTS)
           AND rp.pair_seq = 1
           AND rp.SHIP_QTY >= rp.ORDER_QTY
         
         UNION ALL
         
         -- 递归步骤：继续分配当前发货单剩余量，或切换到下一个发货单
         SELECT 
             CASE 
                 WHEN a.REMAINING_SHIP_QTY > 0 THEN a.SHIPMENT
                 ELSE (SELECT MIN(SHIPMENT) FROM SHIPMENTS WHERE SHIPMENT > a.SHIPMENT)
             END AS SHIPMENT,
             CASE 
                 WHEN a.REMAINING_SHIP_QTY > 0 THEN rp.ORDERID
                 ELSE rp_next.ORDERID
             END AS ORDERID,
             CASE 
                 WHEN a.REMAINING_SHIP_QTY > 0 THEN LEAST(a.REMAINING_SHIP_QTY, rp.ORDER_QTY)
                 ELSE LEAST(rp_next.SHIP_QTY, rp_next.ORDER_QTY)
             END AS ALLOCATED_QTY,
             CASE 
                 WHEN a.REMAINING_SHIP_QTY > 0 THEN a.REMAINING_SHIP_QTY - LEAST(a.REMAINING_SHIP_QTY, rp.ORDER_QTY)
                 ELSE rp_next.SHIP_QTY - LEAST(rp_next.SHIP_QTY, rp_next.ORDER_QTY)
             END AS REMAINING_SHIP_QTY,
             CASE 
                 WHEN a.REMAINING_SHIP_QTY > 0 THEN a.ALLOCATED_ORDERS || ',' || rp.ORDERID
                 ELSE rp_next.ORDERID
             END AS ALLOCATED_ORDERS,
             CASE 
                 WHEN a.REMAINING_SHIP_QTY > 0 THEN rp.pair_seq
                 ELSE 1
             END AS current_pair_seq
         FROM allocation a
         -- 匹配当前发货单的下一个未分配订单
         LEFT JOIN ranked_pairs rp 
             ON rp.SHIPMENT = a.SHIPMENT
             AND rp.pair_seq = a.current_pair_seq + 1
             AND NOT INSTR(a.ALLOCATED_ORDERS, rp.ORDERID) > 0
             AND a.REMAINING_SHIP_QTY > 0
         -- 切换到下一个发货单的首个未分配订单
         LEFT JOIN ranked_pairs rp_next 
             ON rp_next.SHIPMENT = (SELECT MIN(SHIPMENT) FROM SHIPMENTS WHERE SHIPMENT > a.SHIPMENT)
             AND rp_next.pair_seq = 1
             AND NOT INSTR(a.ALLOCATED_ORDERS, rp_next.ORDERID) > 0
             AND a.REMAINING_SHIP_QTY = 0
         WHERE (rp.SHIPMENT IS NOT NULL OR rp_next.SHIPMENT IS NOT NULL)
     )
-- 提取有效分配结果
SELECT 
    SHIPMENT,
    ORDERID,
    ALLOCATED_QTY AS ORDER_QTY
FROM allocation
WHERE ALLOCATED_QTY > 0
ORDER BY SHIPMENT;

核心逻辑说明 #

1. ranked_pairs：为每个发货单内的订单按优先级降序编号，确保递归时按优先级顺序处理。
2. 递归CTE（allocation）：
   • 初始阶段：从第一个发货单的最高优先级订单开始分配，记录已分配订单列表和发货单剩余量。
   • 递归阶段：
     • 若当前发货单还有剩余量，继续匹配该发货单的下一个未被分配的高优先级订单，更新剩余量和已分配列表。
     • 若当前发货单量已用尽，自动切换到下一个发货单，重复分配逻辑。
3. 结果过滤：剔除未产生有效分配的记录，得到最终匹配结果。

执行结果 #

该方案无需创建临时表，完全通过SQL实现实时计算，支持调整优先级算法后即时查看更新结果。

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