如何用MySQL查询mutation表并生成带期初/期末余额的库存报表？

问题背景 #

现有一张存储交易记录的mutation表，表结构及数据如下：

需要查询指定日期范围（例：2024-01-01至2024-01-31）内的数据，生成包含商品ID、日期、期初余额、当日入库、当日出库、期末余额的报表，格式如下：

解决方案 #

可以通过分组汇总当日变动 + 窗口函数计算累计余额实现需求，以下是完整的MySQL查询语句：

SET @start_date = '2024-01-01';
SET @end_date = '2024-01-31';

WITH daily_mutations AS (
    SELECT
        Item_ID,
        M_Date AS entry_date,
        SUM(CASE WHEN Qty > 0 THEN Qty ELSE 0 END) AS Mutation_In,
        SUM(CASE WHEN Qty < 0 THEN Qty ELSE 0 END) AS Mutation_Out,
        SUM(Qty) AS daily_total
    FROM mutation
    WHERE M_Date BETWEEN @start_date AND @end_date
    GROUP BY Item_ID, M_Date
    ORDER BY Item_ID, entry_date
),
balance_calculations AS (
    SELECT
        Item_ID,
        entry_date,
        Mutation_In,
        Mutation_Out,
        SUM(daily_total) OVER (PARTITION BY Item_ID ORDER BY entry_date) AS cumulative_total
    FROM daily_mutations
)
SELECT
    Item_ID,
    entry_date,
    COALESCE(LAG(cumulative_total) OVER (PARTITION BY Item_ID ORDER BY entry_date), 0) AS b_balance,
    Mutation_In,
    Mutation_Out,
    cumulative_total AS e_balance
FROM balance_calculations
ORDER BY Item_ID, entry_date;

语句解释 #

1. 定义日期参数：通过SET语句设置查询的起始和结束日期，方便后续调整。
2. daily_mutations CTE：按商品ID和日期分组，汇总当日的入库（正数量之和）、出库（负数量之和）以及当日总变动量。
3. balance_calculations CTE：使用窗口函数SUM() OVER (PARTITION BY Item_ID ORDER BY entry_date)计算截止到当前日期的累计余额，作为当日的期末余额。
4. 最终查询：
   • 用LAG()窗口函数获取上一日的累计余额，作为当日的期初余额；如果是商品的第一笔记录，通过COALESCE将期初余额设为0。
   • 直接引用之前计算的入库、出库和累计余额，组成最终报表格式。

注意事项 #

• 确保M_Date字段为DATE类型，避免日期格式匹配问题。
• 如果需要包含日期范围内无交易的日期（即显示余额但变动为0），需要额外生成日期序列并左连接原表，可根据实际需求调整。

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