在MySQL中将会计数据的期末余额转换为期初余额

针对将月度期末余额转为下月期初余额的需求，结合你实际数据（多账户、多年度、每日多笔交易）的情况，可通过窗口函数+月度余额聚合的方式实现，具体步骤如下：

1. 先聚合每月账户的期末余额 #

如果原始表（假设表名为account_balances）包含每日交易记录，首先需要提取每个账户每月最后一天的余额作为月度期末余额：

WITH monthly_end_balances AS (
    SELECT
        Date,
        Account,
        Name,
        Balance
    FROM (
        SELECT
            Date,
            Account,
            Name,
            Balance,
            -- 按账户+月份分组，取当月最后一条记录作为期末余额
            ROW_NUMBER() OVER (PARTITION BY Account, DATE_FORMAT(Date, '%Y-%m') ORDER BY Date DESC) AS rn
        FROM account_balances
    ) t
    WHERE rn = 1
)

2. 生成期初与期末余额 #

基于上述月度期末余额表，使用LAG()窗口函数获取上一期的余额作为本期期初：

SELECT
    Date,
    Account,
    Name,
    -- 取同账户上一期的期末余额作为本期期初，第一期无数据则返回NULL
    LAG(Balance) OVER (PARTITION BY Account ORDER BY Date) AS `Beginning Balance`,
    Balance AS `Ending Balance`
FROM monthly_end_balances
ORDER BY Account, Date;

完整SQL语句 #

将两个步骤合并，得到最终可执行的SQL：

WITH monthly_end_balances AS (
    SELECT
        Date,
        Account,
        Name,
        Balance
    FROM (
        SELECT
            Date,
            Account,
            Name,
            Balance,
            ROW_NUMBER() OVER (PARTITION BY Account, DATE_FORMAT(Date, '%Y-%m') ORDER BY Date DESC) AS rn
        FROM account_balances
    ) t
    WHERE rn = 1
)
SELECT
    Date,
    Account,
    Name,
    LAG(Balance) OVER (PARTITION BY Account ORDER BY Date) AS `Beginning Balance`,
    Balance AS `Ending Balance`
FROM monthly_end_balances
ORDER BY Account, Date;

关键注意事项 #

• 如果你的Date字段是字符串格式（如示例中的30 Appril 2020），必须先转为MySQL可识别的日期格式，否则排序会出错。可使用STR_TO_DATE()转换，调整后的SQL如下：

  WITH monthly_end_balances AS (
      SELECT
          DATE_FORMAT(formatted_date, '%d %M %Y') AS Date,
          Account,
          Name,
          Balance
      FROM (
          SELECT
              STR_TO_DATE(Date, '%d %M %Y') AS formatted_date,
              Account,
              Name,
              Balance,
              ROW_NUMBER() OVER (PARTITION BY Account, DATE_FORMAT(formatted_date, '%Y-%m') ORDER BY formatted_date DESC) AS rn
          FROM account_balances
      ) t
      WHERE rn = 1
  )
  SELECT
      Date,
      Account,
      Name,
      LAG(Balance) OVER (PARTITION BY Account ORDER BY STR_TO_DATE(Date, '%d %M %Y')) AS `Beginning Balance`,
      Balance AS `Ending Balance`
  FROM monthly_end_balances
  ORDER BY Account, STR_TO_DATE(Date, '%d %M %Y');
  

• 确保Account字段是准确的分组依据，避免跨账户错误取数。
• 如果月度余额需要通过当月交易累加计算，可调整monthly_end_balances子查询的逻辑，比如用SUM()聚合交易金额得到期末余额。

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