财务报表中如何用SQL计算期初余额与累计发生额？-SQL-PHP中文网

用sum() over()算累计发生额最稳，关键要显式order by唯一时序字段并加rows unbounded preceding；期初余额应取上期截止日累计最大值；金额须统一借贷方向；分组累计需用partition by配合order by。

财务报表中如何用sql计算期初余额与累计发生额？

用 `SUM() OVER()` 窗口函数算累计发生额最稳

直接在明细表上按时间排序累加，比自关联或子查询快得多，也避免了因日期重复、空值导致的错位。关键不是“怎么加”，而是“按什么顺序加”——必须显式指定 ORDER BY，且字段要能唯一确定时序（比如用 trans_date + id 防重）。

常见错误是只写 SUM(amount) OVER (ORDER BY trans_date)，结果同一天多笔就全加到同一行里，实际应加 ROWS UNBOUNDED PRECEDING 显式声明范围，更安全：

SELECT 
  trans_date,
  amount,
  SUM(amount) OVER (ORDER BY trans_date, id ROWS UNBOUNDED PRECEDING) AS cum_amount
FROM journal_entries;

注意：如果 trans_date 是 DATE 类型，但业务要求按凭证号顺序（如 voucher_no 字符串），那就得用 ORDER BY voucher_no，不能硬套日期。

期初余额不能靠 `WHERE date 直接过滤后求和`

因为期初余额是“截至某一时点的静态快照”，不是“某时段内发生的总和”。直接过滤再 SUM() 会漏掉那些在期初前已发生、但尚未结转或被冲销的余额项（比如预收账款跨年未确认收入）。

正确做法是取“上一会计期间期末余额”，即上期的累计余额最后一条记录。通常需先查出上期截止日（如 '2023-12-31'），再取该日及之前所有记录的累计值最大值：

SELECT MAX(cum_amount) AS opening_balance
FROM (
  SELECT SUM(amount) OVER (ORDER BY trans_date, id) AS cum_amount
  FROM journal_entries 
  WHERE trans_date <p>这个 <code>MAX()</code> 不是凑数——它等价于“截至该日最后一笔过账后的余额”，才是真正的期初值。别用 <code>LAST_VALUE()</code>，它依赖框架定义，不同数据库行为不一致。</p><h3>科目有借贷方向时，<code>amount</code> 必须统一为“借方正、贷方负”存储</h3><p>否则 <code>SUM()</code> 累加毫无意义。财务系统里常见两种设计：一种用 <code>dr_amount</code>/<code>cr_amount</code> 两字段，另一种用单字段 <code>amount</code> + <code>direction</code>（'D'/'C'）。后者更利于窗口计算，但必须在聚合前转换：</p>

若原始是 dr_amount 和 cr_amount，先用 COALESCE(dr_amount, 0) - COALESCE(cr_amount, 0) 得净额
若原始是 amount + direction，用 CASE WHEN direction = 'D' THEN amount ELSE -amount END
千万注意 NULL 处理：COALESCE() 或 NULLIF() 比直接加减更安全

没做这步转换就跑累计，结果可能正负颠倒，而且难以排查——因为数字看起来“合理”，只是方向反了。

报表需要按科目分组累计？`PARTITION BY` 要加在窗口函数里，别加在 `GROUP BY`

比如要每个科目单独算累计发生额，很多人会写 GROUP BY account_code 再套 SUM()，但这只能得出总额，丢失每笔明细的中间累计过程。真需求是“每笔凭证在所属科目内的累计值”，必须用：

SUM(amount) OVER (
  PARTITION BY account_code 
  ORDER BY trans_date, id
  ROWS UNBOUNDED PRECEDING
) AS cum_by_account

PARTITION BY 和 ORDER BY 必须同时存在，否则要么全表混算，要么报错。另外，如果科目层级多（如总账科目+明细科目），确保 PARTITION BY 的字段粒度匹配报表维度，不然会出现“应收账款_华为”和“应收账款_阿里”被分开累计，但报表要的是“应收账款”合计——这时得先汇总到上级科目再算累计。

真正麻烦的不是语法，而是业务规则对“累计起点”的定义：是按凭证日期？过账日期？还是审批通过日？这些字段一旦选错，整个累计线就偏了，而且很难从最终数字反推原因。