使用CTE实现润滑油库存按物料分组统计累计出库及剩余库存的SQL查询问题

实现方案 #

你需要用滑动窗口累加函数按物料分组统计历史累计出库量，再用期初库存减去累计出库量得到实时剩余库存，修改后完整SQL如下：

WITH cte AS
(
SELECT 
    dbo.ParseEnum('LubeOilType',SL.LubeOilType) AS MaterialName,
    SL.OpeningStock, 
    Closingstock = (CASE WHEN SL.LubeOilType = 0 THEN DI1.EngineOilQty WHEN SL.LubeOilType = 1 THEN DI2.GearOilQty WHEN SL.LubeOilType = 2 THEN DI3.RearAxleOilQty END), 
    SL.Remarks,
    SL.CreatedOn, 
    SL.LastModifiedOn,
    -- 行号按物料分组+业务发生时间排序，保证出库顺序正确
    ROWNUM = ROW_NUMBER() OVER (PARTITION BY SL.LubeOilType ORDER BY SL.CreatedOn ASC),
    ROWNUM2 = ROW_NUMBER() OVER (ORDER BY SL.LubeOilStockLedgerId)
FROM LubeOilStockLedger SL 
LEFT JOIN DailyLubeOilIssueRegister DI1 
    ON SL.LubeOilStockLedgerId = DI1.EngineOilStockId 
LEFT JOIN DailyLubeOilIssueRegister DI2
    ON SL.LubeOilStockLedgerId = DI2.GearOilQtyStockId 
LEFT JOIN DailyLubeOilIssueRegister DI3 
    ON SL.LubeOilStockLedgerId = DI3.RearAxleOilStockId     
)
SELECT 
    MaterialName,
    OpeningStock,
    Closingstock,
    IssueQty = Closingstock,
    -- 按物料分组累计出库量
    CumulativeIssueQty = SUM(Closingstock) OVER (PARTITION BY MaterialName ORDER BY CreatedOn ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
    -- 剩余库存=期初库存-累计出库量
    RemainingStock = OpeningStock - SUM(Closingstock) OVER (PARTITION BY MaterialName ORDER BY CreatedOn ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
    Remarks,
    CreatedOn,
    LastModifiedOn,
    ROWNUM,
    ROWNUM2
FROM cte

关键逻辑说明 #

• 窗口函数SUM() OVER(PARTITION BY MaterialName ORDER BY CreatedOn ASC)会按物料名称分组，按照出库时间从早到晚累加当期的IssueQty，得到到当前行为止的总出库量
• 调整了行号的排序规则为按CreatedOn排序，保证出库顺序和实际业务发生顺序一致，避免累计结果出错
• 如果同一种物料的期初库存是固定值，直接用期初值减去累计出库量即可得到实时剩余库存，符合你给出的计算逻辑

内容的提问来源于stack exchange，提问作者Prabhat-VS