Oracle查询中逐行扣减Get_Qty订单配额不更新表的实现方法

原查询的问题在于直接将每行商品数量减去整单总配额，未按商品顺序累计扣减，我们可以通过Oracle窗口函数实现需求：

实现代码 #

SELECT 
    order_no,
    item,
    qty,
    get_qty,
    GREATEST(qty - LEAST(qty, GREATEST(get_qty - NVL(prev_sum, 0), 0)), 0) AS qty_result
FROM (
    SELECT 
        o.order_no,
        o.item,
        o.qty,
        Get_Qty(o.order_no) AS get_qty,
        -- 计算当前订单下，当前商品之前所有商品的累计QTY
        SUM(o.qty) OVER (PARTITION BY o.order_no ORDER BY o.item ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_sum
    FROM Orders o
) t
ORDER BY order_no, item;

逻辑说明 #

• 内层查询先按订单号分组，按商品扣减优先级排序（示例默认按ITEM编码升序，你可根据实际规则调整ORDER BY后的字段），计算当前行之前所有商品的累计QTY值prev_sum，同时取出对应订单的总配额
• 外层计算当前行可扣减的最大量：先算配额扣完之前商品后剩下的余量，取余量和当前商品QTY的较小值作为实际扣减量
• 用GREATEST函数处理边界情况，当扣减量超过商品数量或配额用尽时，结果取0
• 最终返回的qty_result就是商品未被扣减的剩余数量，完全符合你给出的示例结果要求

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