SQL查询实现：关联两张表按订单+商品维度分配计算可发数量

云上老周

2026-9-25

SQL 订单商品分配量计算实现方案

实现思路

第一步：关联订单商品需求表（表A）和订单总可分配量表（表B），获取每个订单对应的总可分配额度
第二步：用开窗函数计算同一订单内按商品排序的累计需求数量，同时获取截至上一个商品的累计需求值
第三步：基于累计值判断当前商品可分配数量：
- 若截至上一个商品的累计需求已经大于等于订单总可分配量，当前商品可发量为0
- 否则取当前商品需求数量和剩余可分配量（总可分配量-上一个累计需求）的较小值

适用范围

支持MySQL 8.0+、PostgreSQL、Spark SQL、Hive等所有支持窗口函数的SQL引擎。

具体SQL代码

WITH a_with_cum AS (
    SELECT 
        a.`order`,
        a.item,
        a.qty AS require_qty,
        b.qty AS total_alloc_qty,
        -- 计算当前订单下按指定规则排序的累计需求，包含当前商品
        SUM(a.qty) OVER (PARTITION BY a.`order` ORDER BY a.item) AS cum_require,
        -- 计算当前订单下截至上一个商品的累计需求，第一个商品默认取0
        COALESCE(SUM(a.qty) OVER (PARTITION BY a.`order` ORDER BY a.item ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prev_cum_require
    FROM table_a a
    LEFT JOIN table_b b ON a.`order` = b.`order`
)
SELECT 
    `order`,
    item,
    CASE 
        WHEN prev_cum_require >= total_alloc_qty THEN 0
        ELSE LEAST(require_qty, total_alloc_qty - prev_cum_require)
    END AS ordered_Qty
FROM a_with_cum
ORDER BY `order`, item;