基于SQL Server实现库存FIFO规则下的负值调整方案求助

你好，我看了你的问题和你写的SQL语句——你的嵌套CASE逻辑太复杂，而且没有正确按照FIFO的顺序（从最早年度到最晚年度）来实现负值抵消，所以得到了错误结果。下面给你一个更清晰、可维护的解决方案，完全符合你的需求：

你有一个库存表，包含以下字段：

• ItemName：商品名称
• CurrentStock：当前库存数量
• Qnty2012andBefore、Qnty2013至Qnty2018：各年度的进销存数量总和

需要完成两个核心需求：

1. 将所有年度列中的负值替换为0
2. 按照FIFO（先进先出）规则，用更早年度的正值抵消后续年度的负值（即先消耗最早的库存来覆盖出库需求）

我们可以通过列转行→按顺序处理抵消→行转列的思路来实现，这个方法逻辑清晰，也更容易维护：

完整SQL代码 #

WITH UnpivotedData AS (
    -- 第一步：把列格式的年度数据转换成行，同时指定年度顺序
    SELECT 
        ItemName,
        CurrentStock,
        -- 给每个年度分配排序序号，确保从早到晚的顺序
        CASE YearColumn
            WHEN 'Qnty2012andBefore' THEN 0
            WHEN 'Qnty2013' THEN 1
            WHEN 'Qnty2014' THEN 2
            WHEN 'Qnty2015' THEN 3
            WHEN 'Qnty2016' THEN 4
            WHEN 'Qnty2017' THEN 5
            WHEN 'Qnty2018' THEN 6
        END AS YearOrder,
        YearColumn,
        QntyValue
    FROM StockTable
    UNPIVOT (
        QntyValue FOR YearColumn IN (
            Qnty2012andBefore, Qnty2013, Qnty2014, Qnty2015,
            Qnty2016, Qnty2017, Qnty2018
        )
    ) AS UnpivotResult
),
ProcessedData AS (
    -- 第二步：计算累计的正值总和和负值总和，用于判断抵消逻辑
    SELECT 
        ItemName,
        YearOrder,
        YearColumn,
        QntyValue,
        -- 累计到当前年度的所有正值总和
        SUM(CASE WHEN QntyValue > 0 THEN QntyValue ELSE 0 END)
            OVER (PARTITION BY ItemName ORDER BY YearOrder ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativePositive,
        -- 累计到当前年度的所有负值的绝对值总和
        SUM(CASE WHEN QntyValue < 0 THEN ABS(QntyValue) ELSE 0 END)
            OVER (PARTITION BY ItemName ORDER BY YearOrder ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeNegative
    FROM UnpivotedData
),
FinalProcessed AS (
    -- 第三步：按FIFO规则计算每个年度处理后的值
    SELECT 
        ItemName,
        YearColumn,
        CASE
            -- 处理正值：先抵消前面未覆盖的负值，剩余部分保留
            WHEN QntyValue > 0 THEN
                GREATEST(
                    QntyValue - GREATEST(CumulativeNegative - LAG(CumulativePositive, 1, 0) OVER (PARTITION BY ItemName ORDER BY YearOrder), 0),
                    0
                )
            -- 处理负值：用前面累计的正值抵消，最多清零
            WHEN QntyValue < 0 THEN
                GREATEST(
                    QntyValue + GREATEST(CumulativePositive - LAG(CumulativeNegative, 1, 0) OVER (PARTITION BY ItemName ORDER BY YearOrder), 0),
                    0
                )
            ELSE 0
        END AS ProcessedQnty
    FROM ProcessedData
)
-- 第四步：把行数据转回列，恢复原表结构
SELECT 
    ItemName,
    CurrentStock,
    MAX(CASE WHEN YearColumn = 'Qnty2012andBefore' THEN ProcessedQnty END) AS Qnty2012andBefore,
    MAX(CASE WHEN YearColumn = 'Qnty2013' THEN ProcessedQnty END) AS Qnty2013,
    MAX(CASE WHEN YearColumn = 'Qnty2014' THEN ProcessedQnty END) AS Qnty2014,
    MAX(CASE WHEN YearColumn = 'Qnty2015' THEN ProcessedQnty END) AS Qnty2015,
    MAX(CASE WHEN YearColumn = 'Qnty2016' THEN ProcessedQnty END) AS Qnty2016,
    MAX(CASE WHEN YearColumn = 'Qnty2017' THEN ProcessedQnty END) AS Qnty2017,
    MAX(CASE WHEN YearColumn = 'Qnty2018' THEN ProcessedQnty END) AS Qnty2018
FROM FinalProcessed
GROUP BY ItemName, CurrentStock
ORDER BY ItemName;

代码逻辑说明 #

1. UnpivotedData：把原表中分散在各列的年度数据转换成单一行记录，同时给每个年度分配一个排序序号，确保我们能按从2012及以前到2018的顺序处理，这是FIFO的核心前提。
2. ProcessedData：计算到每个年度为止的累计正值总和（可用于抵消的库存）和累计负值总和（需要被抵消的出库量），为后续的抵消计算提供数据。
3. FinalProcessed：
   • 对于正值：先用来覆盖前面未被抵消的负值，剩下的部分保留为最终值
   • 对于负值：用前面累计的可用正值来抵消，最多将该年度的值清零（符合需求中"所有负值替换为0"的要求）
4. 最后通过行转列操作，把处理后的行数据恢复成原表的列格式，得到符合预期的结果。

这个方案的优势是逻辑清晰，当后续新增年度时，只需要在Unpivot部分和排序CASE中添加对应的年度即可，不需要修改复杂的嵌套逻辑。

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