基于跨表关联计算周度库存的SQL查询报错求助

现有SQL的错误点 #

第一段SQL错误 #

1. 语法结构混乱：CTE定义后，SELECT语句中Production字段末尾多了逗号，触发语法错误；CASE语句被放在SELECT之外，不符合SQL语法规范。
2. 未定义别名：引用了不存在的别名w（w.Plant），数据库无法识别该对象。
3. 逻辑不完整：仅处理了Week=0和Week=1的情况，未覆盖后续所有周的库存计算需求。

第二段SQL错误 #

1. 语法错误：JOIN后直接使用CASE语句，不符合SQL语法，CASE应作为字段放在SELECT列表中。
2. 未定义对象：引用了未声明的Table_A1，数据库无法解析该名称。
3. 逻辑缺失：仅尝试填充Week=0的库存，未实现后续周基于上一周库存的累积计算逻辑。

正确解决方案 #

要实现你的需求（Week=0用TABLE A的库存，后续周基于上一周库存计算(上一周Stock + Production) - Sales），可以使用窗口函数的累积求和实现，以下是适用于PostgreSQL等支持窗口函数的数据库的代码：

WITH initial_stock_data AS (
    -- 获取每个Product+Plant的初始库存
    SELECT Product, Plant, Stock FROM TABLE_A
),
weekly_data AS (
    SELECT 
        b.Product,
        b.Plant,
        b.Week,
        b.Sales,
        b.Production,
        -- Week=0时取初始库存，其他周初始值为0
        CASE WHEN b.Week = 0 THEN isd.Stock ELSE 0 END AS initial_stock,
        -- Week>0时计算当周库存增量，Week=0时增量为0
        CASE WHEN b.Week > 0 THEN (b.Production - b.Sales) ELSE 0 END AS weekly_change
    FROM TABLE_B b
    LEFT JOIN initial_stock_data isd
        ON b.Product = isd.Product AND b.Plant = isd.Plant
)
SELECT 
    Product,
    Plant,
    Week,
    Sales,
    Production,
    -- 按Product+Plant分组，按Week排序，累积计算库存
    SUM(initial_stock + weekly_change) OVER (
        PARTITION BY Product, Plant 
        ORDER BY Week 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Stock
FROM weekly_data
ORDER BY Product, Plant, Week;

代码逻辑说明 #

1. initial_stock_data CTE：提取TABLE A中的初始库存数据，用于关联TABLE B的Week=0记录。
2. weekly_data CTE：关联TABLE B和初始库存数据，拆分出初始库存值和每周的库存增量（Production - Sales）。
3. 窗口函数计算累积库存：使用SUM() OVER()按产品和工厂分组、按周排序，累加初始库存和每周增量，得到每一周的最终库存值。

如果需要将计算结果更新到TABLE B的Stock字段中，可以使用以下更新语句（以PostgreSQL为例）：

-- 先确保TABLE_B有Stock字段
ALTER TABLE TABLE_B ADD COLUMN IF NOT EXISTS Stock INT;

-- 使用CTE计算库存并更新
WITH calculated_stock AS (
    WITH initial_stock_data AS (
        SELECT Product, Plant, Stock FROM TABLE_A
    ),
    weekly_data AS (
        SELECT 
            b.Product,
            b.Plant,
            b.Week,
            CASE WHEN b.Week = 0 THEN isd.Stock ELSE 0 END AS initial_stock,
            CASE WHEN b.Week > 0 THEN (b.Production - b.Sales) ELSE 0 END AS weekly_change
        FROM TABLE_B b
        LEFT JOIN initial_stock_data isd
            ON b.Product = isd.Product AND b.Plant = isd.Plant
    )
    SELECT 
        Product,
        Plant,
        Week,
        SUM(initial_stock + weekly_change) OVER (
            PARTITION BY Product, Plant 
            ORDER BY Week 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Stock
    FROM weekly_data
)
UPDATE TABLE_B b
SET Stock = cs.Stock
FROM calculated_stock cs
WHERE b.Product = cs.Product AND b.Plant = cs.Plant AND b.Week = cs.Week;

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