如何在SQL中基于上一行更新库存表的列数据

你有一张库存数据表，第一行数据正确，但其余行的初始库存需要等于前一天的最终库存，且最终库存为当日初始库存加上received_sold的值。

源数据 #

initial inventory   date    received_sold   final inventory
20                  1/1/23       -1           19
20                  1/2/23       0            20
20                  1/3/23       4            24
20                  1/4/23       2            22
20                  1/5/23       -2           18

预期结果 #

initial inventory   date    received_sold   final inventory
20                  1/1/23       -1           19
19                  1/2/23       0            19
19                  1/3/23       4            23
23                  1/4/23       2            25
25                  1/5/23       -2           23

方法1：递归CTE（适用于多数SQL数据库：PostgreSQL、SQL Server、MySQL 8+、Oracle） #

递归CTE是处理这种递推逻辑最直观的方式，分为锚点成员（第一行正确数据）和递归成员（后续行继承前一日结果）：

WITH recursive_inventory AS (
    -- 锚点成员：取日期最早的一行作为初始数据
    SELECT 
        "initial inventory" AS initial_inventory,
        date,
        received_sold,
        "final inventory" AS final_inventory
    FROM inventory
    ORDER BY date
    LIMIT 1

    UNION ALL

    -- 递归成员：关联前一日结果，计算当日库存
    SELECT 
        prev.final_inventory AS initial_inventory,
        curr.date,
        curr.received_sold,
        prev.final_inventory + curr.received_sold AS final_inventory
    FROM recursive_inventory prev
    JOIN inventory curr
        ON curr.date = (
            SELECT MIN(date) 
            FROM inventory 
            WHERE date > prev.date
        )
)
SELECT * FROM recursive_inventory ORDER BY date;

说明：

• 如果你的日期格式无法直接比较，需要先转换为标准日期类型（如TO_DATE(date, 'MM/DD/YY')）；
• 递归逻辑严格按照日期顺序逐行推导，确保初始库存继承前一日最终值。

方法2：窗口函数（更简洁，适用于支持窗口函数的数据库） #

观察规律可知：每日最终库存 = 第一天初始库存 + 从第一天到当日的received_sold累积和，当日初始库存即为前一日的最终库存。基于此可以用窗口函数快速计算：

SELECT
    -- 第一行保留原初始值，其余行取前一日最终库存作为当日初始值
    CASE 
        WHEN ROW_NUMBER() OVER(ORDER BY date) = 1 THEN "initial inventory"
        ELSE LAG(calculated_final) OVER(ORDER BY date)
    END AS "initial inventory",
    date,
    received_sold,
    calculated_final AS "final inventory"
FROM (
    SELECT
        "initial inventory",
        date,
        received_sold,
        -- 计算当日最终库存：第一天初始值 + 到当日的received_sold累积和
        FIRST_VALUE("initial inventory") OVER(ORDER BY date) 
        + SUM(received_sold) OVER(ORDER BY date) AS calculated_final
    FROM inventory
) AS sub
ORDER BY date;

说明：

• 子查询用SUM() OVER()计算累积和，结合第一天初始值得到当日最终库存；
• 外层用LAG()窗口函数获取前一日最终库存，作为当日初始库存。

若需更新原表（以MySQL为例） #

如果要直接修正原表的数据，可先将计算结果存入临时表，再关联更新：

-- 创建临时表存储计算后的正确数据
CREATE TEMPORARY TABLE temp_inventory AS
WITH recursive_inventory AS (
    SELECT 
        "initial inventory" AS initial_inventory,
        date,
        received_sold,
        "final inventory" AS final_inventory
    FROM inventory
    ORDER BY date
    LIMIT 1

    UNION ALL

    SELECT 
        prev.final_inventory,
        curr.date,
        curr.received_sold,
        prev.final_inventory + curr.received_sold
    FROM recursive_inventory prev
    JOIN inventory curr
        ON curr.date = (
            SELECT MIN(date) FROM inventory WHERE date > prev.date
        )
)
SELECT * FROM recursive_inventory ORDER BY date;

-- 更新原表数据
UPDATE inventory i
JOIN temp_inventory t
    ON i.date = t.date
SET 
    i."initial inventory" = t.initial_inventory,
    i."final inventory" = t.final_inventory;

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