SQL查询如何实现库存INVENTORY_COUNT的逆序累加RUNNING_TOTAL字段

该需求完全可以通过SQL窗口函数实现，核心是调整累计求和的窗口范围即可匹配你要的反向累加效果，修改后完整代码如下：

WITH base_data AS (
    SELECT 
     ROW_NUMBER() OVER(ORDER BY Date) ROW_NUM, 
     A.Change as 'CHANGE', 
     A.InventoryCount AS 'INVENTORY_COUNT', 
     A.Date AS 'DATE', 
     A.Users AS 'USER' 
    FROM(
        SELECT 
        'Stocktake' as 'Change',
        STK.stock_count as 'InventoryCount',
        stk.time_stamp as 'Date',
        STK.count_user as 'Users'
        from IWS_STOCK_TAKE STK WHERE product_code='F100020D'

        UNION
        SELECT
        'Stock pick' as 'Change',
        -1 * abs(picks.PickedQty) as 'InventoryCount',
        picks.picked_time as 'Date',
        picks.PickedBy as 'Users'
        FROM IWS_ORDER_PICKS picks WHERE ProdCode='F100020D'

        UNION
        SELECT
        'Purchase receipt' as 'Change',
        rcv.RECEIVED_QTY as 'InventoryCount',
        rcv.RECEIVED_TIMESTAMP as 'Date',
        rcv.RECEIVER as 'Users'
        FROM IWS_PURCHASE_RECEIVED rcv where PRODUCT_CODE='F100020D'

        UNION
        SELECT
        'Stock Adjustment' as 'Change',
        ADJ.VAR 'InventoryCount',
        ADJ.PROCESSED_DATE as 'Date',
        ADJ.USER_ID as 'Users'
        FROM IWS_STOCK_TAKE_ADJUSTMENTS ADJ where PRODUCT_CODE='F100020D'

    ) A
)
SELECT 
    ROW_NUM AS 'ROW NUMBER',
    CHANGE,
    INVENTORY_COUNT,
    SUM(INVENTORY_COUNT) OVER (ORDER BY ROW_NUM ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RUNNING_TOTAL,
    DATE,
    USER
FROM base_data
ORDER BY ROW_NUM

逻辑说明 #

• 先将原有查询封装为CTE base_data，保留你原来按日期升序生成行号的逻辑，保证除RUNNING_TOTAL外其余字段输出和原有逻辑完全一致
• 新增的RUNNING_TOTAL字段使用了带范围限定的窗口SUM函数：ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 表示求和范围是当前行到整个结果集的最后一行，刚好符合你要求的「从最后一条记录开始向上累加」的规则，计算结果和你给出的示例完全匹配

可选优化 #

如果你确认几类库存流水没有重复记录的场景，可以把原来的UNION替换为UNION ALL，能大幅提升查询性能。如果使用的是不支持窗口函数的旧版本数据库，可以改用子查询自关联的方式实现，只是查询性能会有所下降。

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