SQL根据表A累计收货量为表B新增匹配ExpectedQtyRecv列

直接按Item字段做左连接会产生笛卡尔积：同一Item值下表A存在多条收货记录，表B的每一行会和该Item下所有表A记录逐一匹配，最终返回行数为表B行数乘对应Item下表A的记录数，因此出现大量重复行，同时也没有实现累计值匹配的业务逻辑。

核心逻辑分为两步：

1. 对表A按Item分组、按收货先后排序，计算每条收货记录对应的累计收货量区间，区间格式为(上一条累计收货量, 当前累计收货量]
2. 用表B左连处理后的表A，关联条件为累计用量落在对应区间内，匹配到则返回对应QtyRecv，匹配不到（即累计用量超过该Item总收货量）则返回99999。

重要提示：表A必须使用能唯一标识收货先后的字段排序（如收货时间、入库批次号、自增主键ID），以下示例中用recv_seq作为占位排序字段，实际使用时请替换为业务上真实的排序字段，否则累计顺序错误会导致结果异常。

支持窗口函数的数据库写法（MySQL 8.0+、PostgreSQL、SQL Server、Hive、Spark SQL等主流数据库均支持） #

WITH a_with_cumulative AS (
    SELECT
        Item,
        QtyRecv,
        COALESCE(
            SUM(QtyRecv) OVER (
                PARTITION BY Item
                ORDER BY recv_seq -- 替换为实际收货排序字段
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ),
            0
        ) AS lower_bound,
        SUM(QtyRecv) OVER (
            PARTITION BY Item
            ORDER BY recv_seq -- 与上方排序字段保持一致
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS upper_bound
    FROM tableA
)
SELECT
    b.Item,
    b.RunningTotalQtyUsed,
    COALESCE(a.QtyRecv, 99999) AS ExpectedQtyRecv
FROM tableB b
LEFT JOIN a_with_cumulative a
  ON b.Item = a.Item
  AND b.RunningTotalQtyUsed > a.lower_bound
  AND b.RunningTotalQtyUsed <= a.upper_bound;

逻辑校验 #

用提供的样例数据代入，表A处理后生成的区间如下：

匹配规则完全符合需求：

• 累计用量55落在(0,100]，匹配100
• 累计用量101、149落在(100,238]，匹配138
• 累计用量250落在(238,359]，匹配121
• 累计用量1、5落在(0,6]，匹配6
• 累计用量9落在(6,10]，匹配4
• 累计用量19落在(10,20]，匹配10
• 累计用量31超过总收货量20，无匹配区间，返回99999

最终返回结果与预期完全一致，且返回行数与表B原始行数完全相同，无重复行。

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