1. 需求拆解财务现金日记账为什么写SQL会头疼做财务系统、ERP、OA里带资金模块的兄弟十有八九都接过这类需求把资金流水按日期排好逐条显示收入、支出然后右边给一列“余额”第一行是上一个工作日的期末余额加第一条流水第二条加第二条滚雪球一样滚下去。业务方管这一列叫“本次余额”本质上就是资金的累计结余。我刚工作那会儿遇到的第一个有点含金量的SQL就是现金日记账。当时用的还是Oracle接收到的需求描述是“给我把现金日记账导出来日期升序同日期的按凭证号排余额要连下去”。我第一反应是这有什么难的把表查出来收入减支出自己心算一下加进去不就行了真正动手才发现SQL里没有“上一行”的概念你想让余额自己滚起来光靠普通查询根本做不到得用变量或者自关联去模拟“逐行累加”的效果。这个标题里写的组合很有意思——sum() over(order by, rownum)。这行SQL老手一看就懂新手八成会卡住。sum() over()是窗口函数里最常用的累加函数order by负责指定累加的顺序而Oracle里的rownum则是这个案例里最微妙的细节它不是为了取行号做分页而是为了保证累加过程的顺序稳定。这篇就围绕这个案例把窗口函数累加的原理、rownum在这里的真实作用、完整的可落地SQL以及我在多个版本Oracle、SQL Server上踩过的坑一次讲透。1.1 业务方要的“余额”到底是什么先明确业务口径不然SQL写出来也是错的。现金日记账的余额通常是这么算的期初余额上一日或者上一个期间账面的结余金额。当日每笔发生额收入增加余额支出减少余额。当前余额期初余额 截止到当前行的收入累计 - 截止到当前行的支出累计。放到SQL里就是把收入和支出都转成带符号的数字收入记正数支出记负数然后对所有行从第一行开始做累加。这就是sum(金额) over(order by 日期, 凭证号)做的事情。举个例子更直观假设期初余额是1000元流水如下表日期凭证号摘要收入支出2024-01-02001收到货款50002024-01-02002办公用品02002024-01-03003收到押金10000业务方要看到的结果是日期凭证号收入支出余额2024-01-02001500015002024-01-02002020013002024-01-03003100002300最后一行2300元等于1000500-2001000。这个“滚雪球”的列就是整个报表的灵魂。业务方每天看的就是它月底对账对的也是它银行余额调节表的核心数据来源同样是它。1.2 传统写法为什么又慢又绕在窗口函数普及之前实现“累计余额”主要有两种路子我现在想起来都觉得头大。第一种是自连接思路是把每一行和它之前的每一行关联起来然后用group by分组累计。逻辑上完全正确SQL写着也短但性能是个灾难。流水表一旦有个几万条相关子查询或者分组连接就会出现严重的笛卡尔积爆炸日记账这个场景的数据量通常是“多年流水、长期保留”跑一次报表几分钟出不来很正常。第二种是PL/SQL游标循环或者在SQL Server里用declare变量逐行累加。这个性能没问题但必须在存储过程或脚本里写循环而且只有Oracle和SQL Server支持可移植性差维护成本也高。关键是它绕开了SQL的集合思想回到了“逐行处理”的老路上。所以后来窗口函数一普及sum() over()几乎是立刻取代了上面两种方案。一条语句一次扫描结果全出来性能还远超自连接。标题里这个SQL能成为经典案例核心就在这个函数上。2. 核心原理sum() over()到底在做什么窗口函数Window Function最早在SQL标准里出现是1999年但真正进入主流数据库的速度很慢。Oracle在9i里率先支持SQL Server从2005版本开始有MySQL直到8.0才总算补上。所以在Oracle环境中讨论窗函数是历史最悠久、案例也最丰富的场景。要理解sum() over()先得搞清楚它和普通聚合函数sum()的区别。2.1 和group by聚合的最大区别谁被折叠了普通写select 日期, sum(金额) from 流水 group by 日期结果是什么每个日期只剩一行所有明细行被折叠了。你再也看不到“每一笔凭证的摘要、收入、支出”这些明细剩下的只有汇总。而窗口函数的写法是select 日期, 凭证号, 摘要, 收入, 支出, sum(收入 - 支出) over(order by 日期, 凭证号) as 余额 from 流水注意同一行查询里既有普通列日期、凭证号、摘要又有聚合列sum计算出来的余额。窗口函数over()的作用就是在明细行不折叠的前提下把一个计算范围窗口的值算出来放在每一行旁边。这个特性特别适合做报表因为它天然保留了所有原始信息。简单说普通sum()是“我只要一个总数”窗口sum()是“每行都能看到从起点到当前行的累计数”。后者就是over(order by ...)带来的效果。2.2 over(order by)里的累计语义over()括号里写order by效果是让窗口从分组的第一行开始一直累加到当前行。这个“当前行”是随着查询结果逐行移动的每一行计算时看到的数据范围都不一样。我经常用一个生活化类比来解释窗口函数就像你在看一段滚动播放的记账流水视频视频播放到哪一行屏幕上显示的“累计余额”就是从开头播到这一刻的总数。视频没播到的那些流水不参与当前行的计算。代码层面order by 日期, 凭证号决定了滚动的顺序。这个顺序极其重要因为如果日期排序不对后面累加的余额全错。这也是为什么标题里单独把order by拎出来讲它不是可选项而是这个SQL的“方向盘”。2.3 为什么这里要带上rownum这是这个案例里最值得展开的细节。Oracle里rownum是个伪列在结果集生成的同时分配序号。它能用来分页where rownum 10但在这个日记账案例里它最重要的作用是固定排序的稳定性。什么叫“排序不稳定”如果order by后面的字段存在重复值比如同一天有多笔凭证或者凭证号有相同的数据库返回这些并列行的顺序是不保证的。执行计划一变、数据量一增同一查询跑两次并列行之间的顺序可能就不一样而sum() over()会严格按照这个顺序做累加。顺序一变余额就变报表就对不上账。有人会问那用order by 日期, 凭证号不就行了吗问题是凭证号虽然在业务上要求唯一但如果表里存在历史数据不规范、或凭证号允许为空的情况重复值就这么产生了。数据库不会报错它只会默默按“物理存储顺序”给你个结果而这个顺序你根本没法预期。标题里写sum() over(order by , rownum)我理解的意思是在子查询里先用rownum给每行分配一个绝对稳定的序号然后窗口函数里用这个序号作为最终排序条件。这样哪怕日期、凭证号有重复rownum也是唯一的累加顺序就100%可控。我在实际开发里还会加一句select t.*, sum(t.金额) over(order by t.rn) as 余额 from ( select 日期, 凭证号, 摘要, 收入, 支出, 收入 - 支出 as 金额, rownum as rn from 流水表 t order by 日期, 凭证号 ) t先在子查询里order by 日期, 凭证号得出业务上正确的顺序再用rownum把这个顺序固化成编号rn外层窗口函数就按rn累加。这个写法在Oracle里非常可靠也是我能把这个方案稳定用在生产报表里的关键。需要说明一个更进阶的替代方案Oracle 9i之后有了row_number()可以更优雅地生成唯一序号比如row_number() over(order by 日期, 凭证号) as rn。标题里用的rownum是更早期、更朴素的写法。正式上线时我更推荐row_number()但理解rownum的逻辑依然是理解Oracle执行原理的必修课。3. 完整实例从建表到SQL落地全流程光讲原理不过瘾我把这个案例从零到一完整走一遍。以Oracle为例建表、插数、写SQL、验结果全程贴代码。3.1 建表与测试数据准备现金日记账的业务表结构我简化成最常见的样子实际项目里无非就是再加些辅助字段。-- 现金流水表 create table cash_flow ( flow_id number, -- 唯一ID voucher_date date, -- 记账日期 voucher_no varchar2(20), -- 凭证号 summary varchar2(100),-- 摘要 income_amt number(18,2) default 0, -- 收入金额 expense_amt number(18,2) default 0 -- 支出金额 ); -- 测试数据故意构造同日多笔、金额不规律的情况 insert into cash_flow values (1, date2024-01-02, 001, 期初结转, 10000.00, 0); insert into cash_flow values (2, date2024-01-02, 002, 收到货款, 5000.00, 0); insert into cash_flow values (3, date2024-01-03, 003, 办公用品采购, 0, 800.00); insert into cash_flow values (4, date2024-01-04, 004, 收到押金, 2000.00, 0); insert into cash_flow values (5, date2024-01-04, 005, 差旅费报销, 0, 1200.50); insert into cash_flow values (6, date2024-01-05, 006, 银行提现, 30000.00, 0); insert into cash_flow values (7, date2024-01-05, 007, 支付供应商货款, 0, 15000.00); commit;这里有个容易忽略的点第一条流水我放的是“期初结转”金额10000元。这张表里没有单独的“期初余额”字段期初余额就用一条特殊流水来表示。这样做的好处是所有计算口径统一期初余额和后续明细共用一套累计逻辑坏处是财务对账时得注意区分别把期初结转当成普通收入流水。3.2 关键SQL逐行累计余额的两种写法先上最朴素、也最能看懂的版本select voucher_date as 日期, voucher_no as 凭证号, summary as 摘要, income_amt as 收入, expense_amt as 支出, income_amt - expense_amt as 发生额, sum(income_amt - expense_amt) over(order by voucher_date, voucher_no) as 余额 from cash_flow order by voucher_date, voucher_no;这个SQL已经把需求解决了逻辑上完全正确。但问题在哪就是前面说的order by voucher_date, voucher_no如果存在并列顺序不稳定。这张测试表里voucher_date和voucher_no都唯一所以跑出的结果没毛病。生产环境呢凭证号可能为空日期可能重复历史数据可能乱过一段时间。所以我生产推荐版是这个select t.voucher_date as 日期, t.voucher_no as 凭证号, t.summary as 摘要, t.income_amt as 收入, t.expense_amt as 支出, t.occur_amt as 发生额, sum(t.occur_amt) over(order by t.rn) as 余额 from ( select cf.*, cf.income_amt - cf.expense_amt as occur_amt, rownum as rn from cash_flow cf order by cf.voucher_date, cf.voucher_no ) t order by t.voucher_date, t.voucher_no;这个写法把“业务排序”和“窗口排序”分成两层。内层子查询先按业务规则排序然后通过rownum生成稳定的rn外层窗口函数不再依赖日期和凭证号排序而是依赖唯一且稳定的序号rn。只要内层排序逻辑不变rn就不会变累加结果就永远一致。这已经不是一句SQL的问题而是一种工程习惯。多个团队里大家写的窗口函数排序条件五花八门谁的能上线后一直不出问题谁的就是好方案。在我维护的报表系统里统一要求窗函数内部必须使用唯一键要么是主键要么是row_number()生成的序号宁可麻烦一点也不能让“看起来正确”的SQL埋雷。3.3 结果验证与余额校验执行上面那段推荐版SQL得到结果日期凭证号摘要收入支出发生额余额2024-01-02001期初结转10000.00010000.0010000.002024-01-02002收到货款5000.0005000.0015000.002024-01-03003办公用品采购0800.00-800.0014200.002024-01-04004收到押金2000.0002000.0016200.002024-01-04005差旅费报销01200.50-1200.5014999.502024-01-05006银行提现30000.00030000.0044999.502024-01-05007支付供应商货款015000.00-15000.0029999.50手动验一下最后一行10000 5000 - 800 2000 - 1200.50 30000 - 15000 29999.50结果正确。我在交付报表前会做两层校验第一层是整表校验用sum(发生额)等于最后一行余额减去第一行发生额加上期初不对更简单的方式是直接用普通聚合验证总额差。看最后一行余额29999.50是否等于全表sum(income_amt) - sum(expense_amt)加上第一条之前的值。因为第一条本身就是期初所以这里直接全表发生额累计就是29999.50和最后一行余额一致说明累加没有漏行、没有多行。第二层是抽查任选中间一行手工把它之前所有的发生额加起来和该行余额比对。比如第4行余额16200.00等于100005000-8002000手工算一致。这两层校验花不了几分钟却能避免SQL写错导致整张报表报废。4. 常见问题与避坑指南这个SQL看起来只有一行但生产环境里跑起来各种边角问题能让你从下午排查到下班。我把这些年遇到的高频坑按“现象-原因-解决办法”整理成一套速查表。4.1 日期并列时余额顺序不稳定这是最隐蔽的一个坑。表现是同样一条SQL同一批数据10点跑出一个余额11点再跑变成另一个余额。原因就是over(order by 日期)里日期相同的那几行累加顺序不确定。比如2024-01-04这一天有两笔流水凭证号004和005。如果数据库先处理004再处理005中间状态余额是16200反过来先处理005再处理004中间状态就变成14999.50。最终结果虽然最后一行一样但过程行不一样一旦业务方截个图、对个账差异就出来了。解决办法就是我前面写的子查询里用rownum或者row_number()固定顺序外层窗口函数按序号累加。记住一句话窗口函数的order by必须唯一不唯一就加序号。4.2 凭证号不连续或为空的处理很多流水表凭证号是独立的序列但历史迁移数据里常常有断号、空号。如果order by voucher_no排序空隙本身没问题但空值在Oracle里默认排在最前面可能导致期初余额被空凭证号的流水挤到后面。遇到这种情况我一般会给凭证号补一个排序辅助列比如nvl(voucher_no, zzz)或者直接在业务表里加一个sort_no字段专门用于排序。排序的事交给数据库但排序的口径必须由业务定不要依赖字典序来猜。4.3 金额精度与舍入问题现金账金额不能有半点误差SQL里我统一用number(18,2)存金额但累加过程中可能有浮点误差。Oracle的number类型对十进制计算处理得比较好但SQL Server的float、MySQL的double可能出现0.01的舍入偏差。我的习惯是涉及金额计算一律用定点数类型绝不用浮点。如果必须从别的系统拿数清洗入库时就转成number(18,2)或decimal(18,2)。计算累计余额时再配合round做最终展示层的四舍五入计算层保持精确累加。另外财务系统里负数用“红字冲销”表示比如支出负值实际是退回了的钱这种业务逻辑要在SQL注释里写清楚不然后来接手的人看到负数容易懵。4.4 不同数据库的语法差异标题里的rownum是Oracle专属换了SQL Server或MySQL语法就不一样了。SQL Server从2005开始支持窗函数写法是sum(...) over(order by ...)但生成序号的函数是row_number()没有rownum伪列。如果想实现同样的效果可以把内层嵌套改为select t.*, sum(t.occur_amt) over(order by t.rn) as 余额 from ( select cf.*, cf.income_amt - cf.expense_amt as occur_amt, row_number() over(order by cf.voucher_date, cf.voucher_no) as rn from cash_flow cf ) tMySQL 8.0同样支持row_number()和sum() over()语法几乎和SQL Server一致。PostgreSQL也类似。所以这套方案切换到非Oracle平台核心思路相同只是把rownum换成row_number()。这里要强调row_number()本身就是窗口函数和sum()可以一起写但顺序很重要。如果你在同一个查询里对两个不同窗口分别计算序号和累计值完全可以select voucher_date, voucher_no, sum(income_amt - expense_amt) over(order by row_number() over(order by voucher_date, voucher_no)) as 余额 from cash_flow;这种嵌套写法Oracle和SQL Server都支持但不建议在生产里用可读性太差。老老实实子查询包一层多写几行维护的人会感谢你。4.5 性能问题别让窗口函数引爆临时表窗函数性能大多数情况下比自连接好但也不是没有坑。如果底层表数据量巨大且over(order by ...)的排序列没有索引Oracle会在排序区或临时表空间做一次全量排序。流水表几百万行时排序能把临时表空间撑爆SQL直接报错。优化方向有两个。第一给排序列建索引比如(voucher_date, voucher_no)复合索引让排序走索引避免额外sort。第二如果按月份查询先WHERE过滤月份再计算累计让窗口变小。注意如果查询条件里过滤掉了一部分行累计结果是基于过滤后的行的不是全表的。具体业务上现金日记账通常是查某个月月初余额要手工带入这点要和业务方确认口径。4.6 期初余额怎么进SQL这个问题几乎每次做财务报表都会被问。两种方案方案一期初余额作为一行流水写入表里比如“期初结转”。优点是SQL不用特殊处理余额从第一行开始自动累加。缺点是要保证每月只写一条期初不能重复。方案二期初余额用参数带入SQL比如with init_amt as (select 10000 as amt from dual)然后把期初和累计值相加select t.voucher_date, t.voucher_no, t.occur_amt, sum(t.occur_amt) over(order by t.rn) :init_amt as 余额 from ...这个写法更灵活不用在流水表里塞期初数据但每次报表都得传参稍麻烦。项目里如果业务方要求“期初余额来自上月末报表”方案二更贴合。我一般是优先用方案一因为数据可追溯出现差异时能直接查期初流水不用翻报表参数。4.7 和lag()、lead()的配合现金日记账有时候需要在余额之外再算“环比发生额”也就是和上一笔的金额差。这时候sum() over()不擅长得用lag()窗口函数select voucher_date, voucher_no, income_amt - expense_amt as occur_amt, sum(income_amt - expense_amt) over(order by rownum) as 余额, (income_amt - expense_amt) - lag(income_amt - expense_amt) over(order by rownum) as diff_amt from cash_flow;lag()取上一行的值lead()取下一行它们是窗口函数家族里和sum()搭配最频繁的两个。做资金变动分析、异常流水检测时经常用到。5. 一次实际项目里的排查实录这个案例虽然标题只有一句话但我在生产环境里真实处理过一个和它几乎一模一样的故障拿出来说说也算给上面那些经验做个落地验证。当时是一家做零售的客户门店几十家每天的现金流水表数据量大概两百万行。月初财务导出上个月的现金日记账发现门店A的账号余额和门店的现金盘点差了30多块不多但财务不接受。我去排查时先把SQL拿出来看发现同事写的是select 门店, 日期, 凭证号, 金额, sum(金额) over(partition by 门店 order by 日期) as 余额 from 流水 where 月份 上月一眼就看出了问题order by 日期没有第二排序条件同一天同门店存在多条流水顺序不稳定。30多块的差异就是同一天里某几笔收入的顺序颠倒了余额列中间过程错了。修复方式就是在子查询里加序号select t.门店, t.日期, t.凭证号, t.金额, sum(t.金额) over(partition by t.门店 order by t.rn) as 余额 from ( select s.*, row_number() over(partition by s.门店 order by s.日期, s.凭证号, s.流水号) as rn from 流水表 s where s.记账月份 上月 ) t order by t.门店, t.日期, t.凭证号;这里有两个关键改动。第一个排序加上了第三级“流水号”确保唯一第二个窗口函数内部改用rn序号而不是直接排日期。改完之后重新跑报表余额和门店现金盘点完全一致。这类问题普遍到什么程度呢我在代码评审里看到过太多“order by 日期”就敢写窗口函数的写法几乎每个都要我提醒一句补个唯一排序列。尤其是财务模块金额字段和顺序字段是同一个数据质量级别的不能有一点点儿含糊。6. 把这条SQL当模板用的扩展思路现金日记账只是窗口函数累加最经典的应用场景之一这个写法抽出来可以套到很多日常需求里。最常见的变体包括库存台账按入库、出库时间排序累计得到当前库存量。只是把金额换成数量SQL结构一模一样。银行对账单余额核对把银行流水按日期排好用sum() over()算出账户余额与银行提供的余额核对。销售业绩累计按销售员和时间维度累计销售额实现“年初至今”的销售额口径。积分流水用户积分变动明细加累计剩余积分做法完全一样多一个partition by user_id。如果要在门店维度分别累计只需要加partition bysum(金额) over(partition by 门店 order by rn) as 门店余额这样每个门店独立计算自己的累计余额互不干扰这是窗口函数另一个核心能力——分组内的累计。现金日记账通常不分区因为期初余额是全账套统一的但多机构、多门店场景下就要用partition by了。我还见过一个实际需求每个分公司要一个截至到当月的累计现金流。这个用sum() over(partition by 分公司 order by 月份 rn)分分钟解决。7. 从实战里总结的几条经验最后讲几条实操经验都是被生产环境教训过后才真正记住的。第一条写窗口函数务必检查order by唯一性。这是整个案例最核心的一条。只要order by字段在数据上不唯一结果过程行就差。别说服自己“数据质量好着呢”数据质量这东西在跨系统对接后谁都说不好。第二条期初余额最好以流水形式入库不要单独存一张参数表。日记账报表要的是“从某一天开始累计”把期初作为第一条流水SQL统一校验也统一。缺点是每个月要自动生成一条期初流水这个用定时任务或者触发器都能解决。第三条金额字段绝对不允许float参与累计。别问为什么我曾经对接过一台ERP里面应收金额是float类型累计到第500多行时差出1分钱财务硬是查了一下午。从那以后所有入账金额字段一律定点数计算层用number展示层再处理格式。第四条测试SQL别只用三五行数据就认为万事大吉。至少构造这些边界情况同一天多笔流水、凭证号为空、收入或支出为0、金额为负数红冲、大金额导致累加值超过10亿。能扛住这些边界的SQL上线才不用天天提心吊胆。第五条报表的最终排序和窗口函数的排序要分开写。外层order by只负责展示顺序窗口函数内部控制计算顺序。很多人习惯窗口函数里排好序就不管了外层不再order by这样数据库是可以的但对读报表的人不友好因为查询结果的行顺序并不一定和窗口顺序一致。明确分开写一层业务排序一层计算排序逻辑清晰得多。现金日记账这个案例SQL本身并不复杂复杂的是里面的业务口径、排序稳定性、精度控制这些东西。把这份SQL吃透本质上不是学会一个函数而是建立一种“逐行加上下文”的思维。以后不管是库存台账、积分余额、账单核对还是更复杂的财务归集都能套上这套逻辑而且知道哪里有坑、怎么避开。这大概就是“一个案例顶十个函数”的意义所在。