如何在MySQL中创建带累计计算的查询以生成资产负债表？

嘿，我来帮你搞定这个问题！你之前用关联查询没得到预期结果，大概率是因为直接JOIN两张表会漏掉某一方有交易、另一方无交易的时间点，而且没法自然按时间顺序累计。核心思路应该是先把两张表的所有交易按时间统一整合，再按时间顺序计算累计余额，下面分两种场景给你具体方案：

一、MySQL 8.0+版本（推荐，用窗口函数） #

如果你的MySQL是8.0及以上版本，窗口函数是最简洁高效的实现方式。先明确几个假设（你可以根据实际业务调整）：

• Invoice表的时间字段是I_Date，I_Total是应收金额（视为资产增加，记为正）
• Receiving表的时间字段是CR_Date，CR_Amount是实收金额（视为资产减少，记为负）

具体SQL如下：

WITH combined_transactions AS (
    -- 提取Invoice的交易记录：日期+正金额
    SELECT 
        I_Date AS transaction_date,
        I_Total AS amount,
        'Invoice' AS transaction_type
    FROM Invoice
    UNION ALL
    -- 提取Receiving的交易记录：日期+负金额（冲减应收）
    SELECT 
        CR_Date AS transaction_date,
        -CR_Amount AS amount,
        'Receiving' AS transaction_type
    FROM Receiving
),
sorted_transactions AS (
    -- 按时间排序，确保累计顺序正确
    SELECT 
        transaction_date,
        amount,
        transaction_type
    FROM combined_transactions
    ORDER BY transaction_date ASC
)
-- 计算每一笔交易后的累计余额
SELECT 
    transaction_date,
    transaction_type,
    amount,
    SUM(amount) OVER (ORDER BY transaction_date) AS cumulative_balance
FROM sorted_transactions;

关键细节说明： #

• UNION ALL用来完整合并两张表的所有交易，不会过滤重复（如果需要去重可以换成UNION，但一般业务场景不需要）
• 窗口函数SUM(amount) OVER (ORDER BY transaction_date)会自动按时间顺序，逐行计算到当前为止的累计余额
• 如果需要按天/月等粒度汇总（比如每天的总交易和累计余额），可以先分组再计算：

  WITH daily_transactions AS (
      SELECT 
          DATE(transaction_date) AS transaction_day,
          SUM(amount) AS daily_total
      FROM combined_transactions
      GROUP BY DATE(transaction_date)
      ORDER BY transaction_day ASC
  )
  SELECT 
      transaction_day,
      daily_total,
      SUM(daily_total) OVER (ORDER BY transaction_day) AS cumulative_balance
  FROM daily_transactions;
  

二、MySQL 5.x版本（无窗口函数，用变量实现） #

如果你的MySQL版本较旧，不支持窗口函数，可以用自定义变量来计算累计：

SELECT 
    transaction_date,
    transaction_type,
    amount,
    @cumulative_balance := @cumulative_balance + amount AS cumulative_balance
FROM (
    -- 先合并并排序所有交易记录
    SELECT 
        I_Date AS transaction_date,
        I_Total AS amount,
        'Invoice' AS transaction_type
    FROM Invoice
    UNION ALL
    SELECT 
        CR_Date AS transaction_date,
        -CR_Amount AS amount,
        'Receiving' AS transaction_type
    FROM Receiving
    ORDER BY transaction_date ASC
) AS sorted_transactions
-- 初始化累计余额变量
CROSS JOIN (SELECT @cumulative_balance := 0) AS init_var;

注意事项： #

• 子查询里的ORDER BY必须生效，否则累计顺序会混乱
• 如果要按日期汇总，同样先在子查询里分组求和后再排序

可灵活调整的点 #

• 如果你的业务逻辑中金额方向和假设相反（比如Invoice是应付、Receiving是付款），只需要调换金额的正负号即可
• 如果时间字段包含时分秒，需要按天统计的话，记得用DATE()函数截断时间
• 可以在SELECT语句中加WHERE amount != 0过滤掉无意义的零金额记录

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