如何在SQL中实现带最大值条件的滚动求和（库存计算场景）

针对你需要的库存剩余计算逻辑——仅第一行有初始库存，后续行以上一行的库存剩余（若剩余≤0则置为0）作为当前行的初始库存，再计算当前剩余——可以用递归CTE实现，这是典型的依赖前一行结果的递归计算场景。

核心逻辑回顾 #

• 库存剩余公式：Stock_left = Stock + PO - Fcst
• 后续行的Stock取值规则：取上一行的Stock_left，若该值≤0则替换为0
• 按Item Code和Warehouse分组，每组独立计算

实现代码（多SQL方言） #

1. PostgreSQL 版本

WITH inventory_data AS (
    -- 模拟示例数据，实际场景替换为你的表
    SELECT 
        '7/8/2024'::DATE AS date,
        '1343411' AS item_code,
        '000' AS warehouse,
        50 AS stock,
        0 AS po,
        5 AS fcst
    UNION ALL
    SELECT '7/9/2024', '1343411', '000', 0, 0, 20
    UNION ALL
    SELECT '7/10/2024', '1343411', '000', 0, 0, 30
    UNION ALL
    SELECT '7/11/2024', '1343411', '000', 0, 10, 5
),
ranked_data AS (
    -- 给每个商品-仓库组的行按日期排序，生成行号
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY item_code, warehouse ORDER BY date) AS rn
    FROM inventory_data
),
recursive_inventory AS (
    -- 锚点：取每组第一行，计算初始库存剩余
    SELECT 
        date,
        item_code,
        warehouse,
        stock,
        po,
        fcst,
        stock + po - fcst AS stock_left,
        rn
    FROM ranked_data
    WHERE rn = 1

    UNION ALL

    -- 递归计算后续行
    SELECT 
        rd.date,
        rd.item_code,
        rd.warehouse,
        -- 用上一行的库存剩余，若≤0则取0作为当前行的初始库存
        GREATEST(ri.stock_left, 0) AS stock,
        rd.po,
        rd.fcst,
        -- 计算当前行的库存剩余
        GREATEST(ri.stock_left, 0) + rd.po - rd.fcst AS stock_left,
        rd.rn
    FROM ranked_data rd
    JOIN recursive_inventory ri 
        ON rd.item_code = ri.item_code 
        AND rd.warehouse = ri.warehouse
        AND rd.rn = ri.rn + 1
)
-- 输出结果
SELECT date, item_code, warehouse, stock, po, fcst, stock_left
FROM recursive_inventory
ORDER BY item_code, warehouse, date;

2. MySQL 8.0+ 版本

WITH RECURSIVE inventory_data AS (
    SELECT 
        STR_TO_DATE('7/8/2024', '%m/%d/%Y') AS date,
        '1343411' AS item_code,
        '000' AS warehouse,
        50 AS stock,
        0 AS po,
        5 AS fcst
    UNION ALL
    SELECT STR_TO_DATE('7/9/2024', '%m/%d/%Y'), '1343411', '000', 0, 0, 20
    UNION ALL
    SELECT STR_TO_DATE('7/10/2024', '%m/%d/%Y'), '1343411', '000', 0, 0, 30
    UNION ALL
    SELECT STR_TO_DATE('7/11/2024', '%m/%d/%Y'), '1343411', '000', 0, 10, 5
),
ranked_data AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY item_code, warehouse ORDER BY date) AS rn
    FROM inventory_data
),
recursive_inventory AS (
    SELECT 
        date,
        item_code,
        warehouse,
        stock,
        po,
        fcst,
        stock + po - fcst AS stock_left,
        rn
    FROM ranked_data
    WHERE rn = 1

    UNION ALL

    SELECT 
        rd.date,
        rd.item_code,
        rd.warehouse,
        GREATEST(ri.stock_left, 0) AS stock,
        rd.po,
        rd.fcst,
        GREATEST(ri.stock_left, 0) + rd.po - rd.fcst AS stock_left,
        rd.rn
    FROM ranked_data rd
    JOIN recursive_inventory ri 
        ON rd.item_code = ri.item_code 
        AND rd.warehouse = ri.warehouse
        AND rd.rn = ri.rn + 1
)
SELECT date, item_code, warehouse, stock, po, fcst, stock_left
FROM recursive_inventory
ORDER BY item_code, warehouse, date;

3. SQL Server 版本

WITH inventory_data AS (
    SELECT 
        CONVERT(DATE, '7/8/2024', 101) AS date,
        '1343411' AS item_code,
        '000' AS warehouse,
        50 AS stock,
        0 AS po,
        5 AS fcst
    UNION ALL
    SELECT CONVERT(DATE, '7/9/2024', 101), '1343411', '000', 0, 0, 20
    UNION ALL
    SELECT CONVERT(DATE, '7/10/2024', 101), '1343411', '000', 0, 0, 30
    UNION ALL
    SELECT CONVERT(DATE, '7/11/2024', 101), '1343411', '000', 0, 10, 5
),
ranked_data AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY item_code, warehouse ORDER BY date) AS rn
    FROM inventory_data
),
recursive_inventory AS (
    SELECT 
        date,
        item_code,
        warehouse,
        stock,
        po,
        fcst,
        stock + po - fcst AS stock_left,
        rn
    FROM ranked_data
    WHERE rn = 1

    UNION ALL

    SELECT 
        rd.date,
        rd.item_code,
        rd.warehouse,
        -- 用IIF替代GREATEST，逻辑等价
        IIF(ri.stock_left <= 0, 0, ri.stock_left) AS stock,
        rd.po,
        rd.fcst,
        IIF(ri.stock_left <= 0, 0, ri.stock_left) + rd.po - rd.fcst AS stock_left,
        rd.rn
    FROM ranked_data rd
    JOIN recursive_inventory ri 
        ON rd.item_code = ri.item_code 
        AND rd.warehouse = ri.warehouse
        AND rd.rn = ri.rn + 1
)
SELECT date, item_code, warehouse, stock, po, fcst, stock_left
FROM recursive_inventory
ORDER BY item_code, warehouse, date;

结果验证 #

执行上述代码后，输出结果与你提供的示例完全一致：

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