SQL查询需求：如何基于剩余总数量计算Balance Qty？

要实现累计扣减后的结余计算，你需要使用SQL窗口函数计算累计发货量，再用初始总量减去累计值得到当前结余。以下是具体解决方案：

核心思路 #

1. 修正表关联逻辑：原查询未添加关联条件会产生笛卡尔积，必须通过采购订单行的关联字段（如purchase_order_line_id）将两张表正确关联。
2. 用SUM() OVER()窗口函数，按采购订单行分组，再按发货顺序（如发货日期、单据ID）排序，计算累计发货量。
3. 用初始总量pl.quantity减去累计发货量，得到当前结余。

示例SQL代码 #

SELECT 
    pl.quantity AS total_qty,
    pr.invoiceqty,
    SUM(pr.invoiceqty) OVER (
        PARTITION BY pl.id  -- 按采购订单行分组，确保同一订单的发货记录归为一组
        ORDER BY pr.ship_date  -- 替换为实际业务中确定发货顺序的字段（如单据ID、发票日期）
    ) AS cumulative_invoiceqty,
    pl.quantity - SUM(pr.invoiceqty) OVER (
        PARTITION BY pl.id 
        ORDER BY pr.ship_date
    ) AS balance_qty
FROM purchaseorderline pl
JOIN replenishmentrequisition pr 
    ON pl.purchase_order_line_id = pr.purchase_order_line_id;  -- 替换为你的实际关联字段

关键说明 #

• 排序字段：ORDER BY后的字段必须能确定发货先后顺序，否则累计逻辑会混乱，导致结余计算错误。
• 关联字段：ON子句中的关联字段需匹配你的表结构，确保只关联同一采购订单行的发货记录。
• 窗口函数范围：多数数据库默认会计算从分组第一条到当前行的累计值，无需额外指定范围，若需显式声明可添加ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

以你给出的示例场景（total qty=100，发货量40、10、25、25），执行后会得到预期结果：

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