基于交易数据表生成按日期的库存汇总SQL查询需求

问题说明 #

现有交易数据表（假设表名为transactions），字段定义如下：

• ProductID：产品唯一标识
• Quantity：交易数量
• ProductType：交易类型（1代表入库，-1代表出库）
• Date：交易发生日期

示例交易数据 #

需要根据输入的ProductID、开始日期、结束日期参数（初始库存为0），生成包含以下字段的库存报表：

• ProductID：产品ID
• Date：交易日期
• Beginning Quantity：交易发生时的期初库存
• Quantity In：本次交易的入库数量（非入库交易显示0）
• Quantity Out：本次交易的出库数量（非出库交易显示0）
• Ending Quantity：交易完成后的期末库存

目标输出示例 #

SQL解决方案（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

WITH transaction_details AS (
    SELECT
        ProductID,
        Date,
        Quantity,
        ProductType,
        -- 计算单条交易对库存的影响：入库加，出库减
        CASE WHEN ProductType = 1 THEN Quantity ELSE -Quantity END AS inventory_change,
        -- 按交易日期排序，保证库存计算顺序正确
        ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Date) AS row_num
    FROM transactions
    WHERE
        ProductID = @TargetProductID -- 替换为目标产品ID参数
        AND Date BETWEEN @StartDate AND @EndDate -- 替换为起止日期参数
)
SELECT
    ProductID,
    Date,
    -- 期初库存：初始为0，后续取之前所有交易的累计库存变化
    COALESCE(
        SUM(inventory_change) OVER (PARTITION BY ProductID ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
        0
    ) AS "Beginning Quantity",
    -- 入库数量：仅入库交易显示对应数量
    CASE WHEN ProductType = 1 THEN Quantity ELSE 0 END AS "Quantity In",
    -- 出库数量：仅出库交易显示对应数量
    CASE WHEN ProductType = -1 THEN Quantity ELSE 0 END AS "Quantity Out",
    -- 期末库存：累计到当前交易的库存变化总和
    COALESCE(
        SUM(inventory_change) OVER (PARTITION BY ProductID ORDER BY row_num),
        0
    ) AS "Ending Quantity"
FROM transaction_details
ORDER BY Date, row_num;

兼容旧版本数据库（如MySQL 5.x）的方案 #

SELECT
    ProductID,
    Date,
    @prev_ending AS "Beginning Quantity",
    CASE WHEN ProductType = 1 THEN Quantity ELSE 0 END AS "Quantity In",
    CASE WHEN ProductType = -1 THEN Quantity ELSE 0 END AS "Quantity Out",
    -- 动态更新期末库存作为下一条交易的期初库存
    @prev_ending := @prev_ending + CASE WHEN ProductType = 1 THEN Quantity ELSE -Quantity END AS "Ending Quantity"
FROM (
    -- 先筛选目标数据并按日期排序
    SELECT *
    FROM transactions
    WHERE
        ProductID = @TargetProductID
        AND Date BETWEEN @StartDate AND @EndDate
    ORDER BY Date
) AS filtered_trans
-- 初始化库存变量为0
CROSS JOIN (SELECT @prev_ending := 0) AS init_vars;

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