尧图网页设计  over(order by rownum)避坑指南)
做财务系统的朋友应该都有体会现金日记账这种功能看着简单真要把它写清楚还挺折腾。业务上无非是“每发生一笔收支余额等于上一笔余额加收入减支出”可这个“上一笔余额”落到SQL里就成了一道典型的累计求和题。我最早是用游标循环一行行算的数据量小还凑合流水一多性能直接拉胯。后来换成sum() over()窗口函数SQL精炼了不少但真正让我栽跟头的是对排序的理解——这也是今天这篇要聊的核心用sum() over(order by rownum)实现现金日记账以及在Oracle里关于rownum的那些坑。这篇文章适合三类人看一是被财务模块累计余额折磨过的后端开发二是刚接触窗口函数、想弄明白over(order by ...)到底怎么累加的SQL新手三是从Oracle往其他数据库迁移时被rownum的差异坑过的朋友。我会把业务模型、SQL原理、完整可跑的案例、还有不同数据库的改写方式都拆开讲清楚。1. 现金日记账的业务模型与SQL思路拆解1.1 现金日记账到底在算什么现金日记账是财务模块里最基础的账簿之一用来逐笔记录企业现金的收入、支出并实时给出余额。它的规则非常固定按业务发生的时间顺序每登记一笔就用上一笔的余额加上本笔收入、减去本笔支出得到最新余额。举个例子一张最基础的现金流水表可能是这样的凭证号日期摘要方向金额0012025-01-06期初余额收入10000.000022025-01-06购买办公用品支出500.000032025-01-07销售货款收入8000.000042025-01-07差旅费报销支出1200.000052025-01-07业务招待费支出1200.00余额的计算逻辑是这样的第一笔后面的余额是10000.00第二笔余额变成10000.00减500.00等于9500.00第三笔变成9500.00加8000.00等于17500.00。每一行的余额都依赖前面所有行的累计结果这天然就是一个“滚动求和”的问题。把这个问题翻译成SQL语言你会发现自己面对的是一个非常典型的场景每一行输出的值不能只由当前行决定还要参考“本行之前所有行”的数据。这跟group by做的分组汇总完全是两码事——分组汇总会把很多行合并成一行而日记账要求每一行都保留同时带上截至当前行的累计值。1.2 为什么线性累加不能靠一组简单聚合完成很多初学者第一反应是那我用子查询把之前所有金额加起来不就行了比如select a.biz_date, a.amount, (select sum(b.amount) from cash_ledger b where b.biz_date a.biz_date or (b.biz_date a.biz_date and b.record_id a.record_id) ) as running_total from cash_ledger a;这个写法逻辑上没错但性能上有大问题。假设表里有N行数据每一行都要去扫描一次前面的数据整体复杂度接近O(N²)。当流水达到几万、几十万条时这条SQL能把数据库拖到怀疑人生。自连接也能做但写法更啰嗦而且同样面临性能瓶颈。现金日记账往往是财务每天都要打开看的页面不可能让用户等几十秒才出结果。窗口函数sum() over()解决的正是这个问题。它可以在一次扫描的过程中把“截至当前行的累计值”算出来不需要反复回表也不需要自己拼子查询。这也是窗口函数名字的由来它开了一个“窗口”这个窗口随当前行移动窗口内是当前行之前的所有行然后在这个窗口上做聚合。1.3 为什么排序是日记账SQL的命门窗口函数本身不关心业务顺序它只认over()里面的order by。所以“排序键”选什么直接决定累计结果对不对。现金日记账的业务顺序说到底就是“时间顺序”。但实际表里往往没有“自然顺序”字段只有一个自增主键比如record_id。有经验的做法是用biz_date加record_id来做排序键。可这里有个隐藏问题——在多笔业务发生在同一天时只有日期没法确定先后就算加了record_id如果未来有数据回补、批量导入主键顺序也不一定等于业务顺序。这时候就需要一个“稳定的、排好序的行号”来当代理排序键。Oracle里的rownum就是干这个用的但它有一个非常大的坑我会在下一节专门讲。搞懂了排序这个SQL就成功了一半。2. sum() over() 的排序逻辑与 rownum 的关键坑2.1 SUM(...) OVER(ORDER BY ...) 的执行过程要理解sum() over()先记住一个结论没有order by的窗口函数窗口是整张表有order by的窗口函数窗口是从第一行到当前行的累计范围。看这个最简单的例子select record_id, amount, sum(amount) over(order by record_id) as running_total from cash_ledger;sum(amount)本身是求和over(order by record_id)告诉数据库“你要按record_id从小到大排序然后对每一行都对它前面的所有行做累加”。所以running_total这一列就是逐行累加的结果。执行顺序上窗口函数是在from、where、group by、having之后执行的。也就是说先选出符合条件的行再对这些行排序并开窗累加。这也意味着窗口函数不能引用同一层级中另一个窗口函数的别名得套一层子查询才能复用。用生活化的类比理解想象你手拿一叠纸质报销单按日期排好序然后拿计算器从第一张开始一张一张往下加。你每加完一张记下的当前总数就是sum() over(order by ...)给那一行的结果。窗口函数帮你自动完成了“一边移动、一边累加”的过程。2.2 排序键不唯一的稳定顺序陷阱很多人在这一步开始踩坑。比如只按biz_date排序select left(record_id, 3) as voucher_no, biz_date, amount, sum(amount) over(order by biz_date) as running_total from cash_ledger;如果同一天有多笔业务比如上面示例里1月7日的两笔支出数据库到底先加1200还是后加1200答案是不确定。当order by的排序键存在重复值时数据库可能按物理存储顺序、也可能按索引顺序、甚至并行执行时结果都不同。也就是说同样的数据跑两次余额可能不一样。这在财务系统里是绝对不允许的。日记账的余额必须精确到每一笔不能出现“相同的流水、不同的余额”这种低级错误。解决办法很直接排序键必须能唯一定位每一行。要么用主键record_id要么用“日期凭证号行号”这类复合键。如果你要保留严格的业务顺序最稳妥的方案是先按业务规则排好序再生成一个连续且唯一的行号最后拿这个行号当窗口函数的排序键。这就是下面要讲的rownum方案。2.3 Oracle ROWNUM 的“先编号后排序”问题Oracle 里有个非常容易误解的伪列叫rownum。很多新手以为它是“物理行号”以为表里的数据固定有一个rownum。事实完全不是这样。rownum是查询结果集生成过程中分配的伪列——每返回一行rownum就加一。它的分配时机是在“行被取出来”的时候而不是在“最终排序完成”的时候。所以如果你写select record_id, biz_date, amount, rownum from cash_ledger order by biz_date desc;你会看到rownum仍然是按原表扫描顺序编号的1、2、3并不会因为order by biz_date desc而重新排序。这个特性坑过无数人。在现金日记账里我们真正想要的是先把数据按日期、凭证号排好再给它们安上连续的序号1、2、3……随后基于这个序号做累计。正确的写法是“三层嵌套”最内层负责业务排序中间层生成固定rownum最外层做sum() over(order by rownum)。如果省略中间层直接在外层写order by rownum那rownum仍然是原始扫描顺序的编号跟业务顺序没有关系余额自然就是错的。这就是很多人在网上搜到这个写法却跑出错误结果的根本原因。3. 实操用 sum() over(order by rownum) 实现现金日记账3.1 准备测试表和数据纸上谈兵没有意义直接来一套可以跑的Oracle示例。先建表create table cash_ledger ( record_id number(10) primary key, biz_date date not null, direction varchar2(2) not null, -- IN 收入 / OUT 支出 amount number(12,2) not null, summary varchar2(200) );插入测试数据为了体现“同一天多笔”的场景1月7日特意放了两笔支出insert into cash_ledger values (1, date 2025-01-06, IN, 10000.00, 期初余额); insert into cash_ledger values (2, date 2025-01-06, OUT, 500.00, 购买办公用品); insert into cash_ledger values (3, date 2025-01-07, IN, 8000.00, 销售货款); insert into cash_ledger values (4, date 2025-01-07, OUT, 1200.00, 差旅费报销); insert into cash_ledger values (5, date 2025-01-07, OUT, 1200.00, 业务招待费); insert into cash_ledger values (6, date 2025-01-08, IN, 2000.00, 其他收入); commit;这里的record_id在实际项目中可能是序列生成的也可能是一张流水凭证表的自增主键。我特意让它均匀递增但你要注意业务顺序并不总是等于主键顺序。更真实的情况是凭证可能跨月补录导致record_id大但日期小。所以在排序时order by biz_date, record_id才是符合业务直觉的做法。3.2 核心SQL三层嵌套写法完整的现金日记账SQL长这样select biz_date, direction, amount, summary, running_seq, nvl( sum( case when direction IN then amount when direction OUT then -amount else 0 end ) over (order by running_seq), 0 ) as balance from ( select record_id, biz_date, direction, amount, summary, rownum as running_seq from ( select record_id, biz_date, direction, amount, summary from cash_ledger order by biz_date, record_id ) ) order by running_seq;执行结果如下biz_datedirectionamountsummaryrunning_seqbalance2025-01-06IN10000.00期初余额110000.002025-01-06OUT500.00购买办公用品29500.002025-01-07IN8000.00销售货款317500.002025-01-07OUT1200.00差旅费报销416300.002025-01-07OUT1200.00业务招待费515100.002025-01-08IN2000.00其他收入617100.00这个结果和手工计算的余额完全一致。重点看中间层先按biz_date, record_id排序再通过rownum生成连续编号running_seq。最外层的sum() over(order by running_seq)拿到的是一个“干净”的、没有任何重复值的排序键累计顺序完全确定。提示running_seq这个中间层字段不一定非要叫这个名可以用任何你喜欢的别名。但强烈建议不要直接写rownum作为最终展示字段因为rownum在不同查询上下文中含义不一样很容易让人误解。3.3 方向金额转换与期初余额处理在这个SQL里case when direction IN then amount when direction OUT then -amount把收、支方向转成了带符号金额。这样做的目的是让sum()可以直接做“收入加、支出减”的运算不需要额外再写复杂逻辑。这里有个细节值得注意nvl(..., 0)不是可有可无的。如果表里没有任何数据或者某一行之前的累计值为NULL窗口函数可能会返回NULL给前端渲染带来麻烦。虽然正常情况下第一行就会累出正数但加上nvl能让输出更稳健也方便后续把余额字段直接用于展示。关于期初余额有两条经验最简单是把期初余额也做成一条direction IN的记录如本例所示。这样所有业务统一处理逻辑最简单。如果期初余额是单独存在参数表里的不体现在流水里那么可以先把期初余额作为变量传入再把累计结果加上这个变量select biz_date, direction, amount, summary, nvl(sum(signed_amount) over (order by running_seq), 0) :initial_balance as balance from (...);第一种方案更适合大多数财务系统因为期初余额本身也要出现在日记账首页供核对做成记录不丢明细。3.4 扩展按日结账与跨天余额现金日记账还有一个常见需求每天下班前看一个“日结余额”。用窗口函数也能轻松做到只需要在over()里加一个partition by biz_dateselect biz_date, direction, amount, summary, sum( case when direction IN then amount when direction OUT then -amount else 0 end ) over (partition by biz_date order by running_seq) as daily_balance from (...);这里的partition by biz_date会把同一天的数据单独分组组内再按running_seq累计。这样每天的第一笔余额从0开始累加看不到上一日的结转数。如果你想要“每天累计到当天为止的总余额”还是用不带partition by的版本然后按日期取最大值即可。跨月结转也是财务系统的经典场景。推荐做法是每个会计期间开始时插入一笔期初余额记录查询时where biz_date 期间开始日期这样窗口函数自然从期初开始累加不需要额外写结转逻辑。4. 不同数据库下的实现差异4.1 OracleROWNUM 的经典写法Oracle里rownum顺手但正如前面提到的必须先排序再编号。完整的Oracle写法就是第3.2节那套三层嵌套不再重复。这套写法在Oracle 11g、12c、19c上都没有问题。Oracle 18c之后也有了row_number()窗口函数如果你更习惯显式的行号函数可以用row_number() over(order by biz_date, record_id)替代中间层的rownum结果一样稳定而且可读性更好select biz_date, direction, amount, summary, sum( case when direction IN then amount when direction OUT then -amount else 0 end ) over (order by running_seq) as balance from ( select record_id, biz_date, direction, amount, summary, row_number() over (order by biz_date, record_id) as running_seq from cash_ledger ) order by running_seq;如果你还在维护老的Oracle系统建议优先用row_number()语义更清晰也方便将来迁移到其他数据库。4.2 SQL ServerROW_NUMBER() 替代SQL Server 没有rownum但它的row_number()是完整的窗口函数从2005版本开始就有了。日记账SQL可以直接这么写select biz_date, direction, amount, summary, sum( case when direction IN then amount when direction OUT then -amount else 0 end ) over (order by running_seq) as balance from ( select record_id, biz_date, direction, amount, summary, row_number() over (order by biz_date, record_id) as running_seq from cash_ledger ) t order by running_seq;注意SQL Server对表别名要求更严格子查询必须加别名比如这里的t。另外SQL Server中order by在子查询里通常不允许直接写除非配合offset或row_number()这类窗口函数使用上面的写法完全绕开了这个限制所以在实际项目中特别常用。4.3 MySQL 8.0 与其他数据库MySQL 在8.0版本之前没有窗口函数只能靠用户变量模拟累计求和写法又丑又容易踩坑-- 老版本MySQL用用户变量不推荐新项目使用 set seq : 0; set balance : 0; select biz_date, direction, amount, seq : seq 1 as running_seq, balance : balance case when direction IN then amount when direction OUT then -amount else 0 end as balance from cash_ledger order by biz_date, record_id;这种写法的balance依赖变量赋值顺序在复杂SQL和并发环境下容易出问题只建议用于临时查询。如果用的是MySQL 8.0写法跟SQL Server几乎一样select biz_date, direction, amount, summary, sum( case when direction IN then amount when direction OUT then -amount else 0 end ) over (order by running_seq) as balance from ( select record_id, biz_date, direction, amount, summary, row_number() over (order by biz_date, record_id) as running_seq from cash_ledger ) t order by running_seq;PostgreSQL、达梦等支持窗口函数的数据库大同小异。达梦因为兼容Oracle甚至连rownum的行为都很接近Oracle可以直接套用前面的Oracle三层结构。数据库行号方式窗口函数支持度推荐写法Oraclerownum / row_number()完整row_number() over(order by ...)SQL Serverrow_number()完整row_number() over(order by ...)MySQL 8.0row_number()完整row_number() over(order by ...)老版本MySQL用户变量不支持建议升级或用变量模拟达梦rownum / row_number()完整兼容Oracle写法5. 常见问题与性能调优实录5.1 余额错乱、顺序漂移怎么排查如果你发现余额算出来和手工核对不一致优先按这个顺序排查确认排序键有没有重复。order by biz_date在一天有多笔业务时是不安全的必须加上record_id或凭证号等唯一字段。检查有没有在排序键上做函数运算。比如order by trunc(biz_date)一旦把日期截断成天多笔同一天的数据又进入“同值”状态顺序漂移会再次出现。确认最内层的排序结果是否真的符合业务预期。如果存在“补录凭证”日期早但ID大要结合业务规则决定按什么排。财务上通常以凭证日期和凭证号为准。我曾经排查过一个线上问题用户看到某些天的余额偶发错乱尤其是月底月初交接时。最后发现是业务系统里存在“红字冲销单”金额为负数方向字段却仍是OUT。如果没有针对负数金额做特殊判断累计结果自然不对。排查时先把方向、金额、排序键全部列出来人工核对前20笔问题很快就暴露了。5.2 大表数据量下的性能优化现金日记账在年末可能有几十万甚至上百万条流水窗口函数的性能不可忽视。窗口函数比游标和自连接快得多因为它只需要一次全表扫描。但扫描是有代价的想让SQL跑得更快核心思路是缩小扫描范围并减少排序成本。加过滤条件查询日记账通常只查某个月或某个期间尽量加上where biz_date between ... and ...。建复合索引在(biz_date, record_id)上建索引能显著降低排序开销。如果查询还带其他筛选条件再考虑把这些筛选字段一起放进索引。避免最内层大量排序order by biz_date, record_id如果能走索引就不需要显式排序否则Oracle会在排序区操作数据量大时临时表空间会吃紧。不要在大表上直接用select *包多层子查询能在外层只取需要的字段就减少不必要的列参与排序。实际案例里把十几万条流水套上上面这套结构后查询从原来的2秒多优化到0.3秒以内效果非常明显。如果你的系统数据量更大还可以考虑按月份分区表让窗口函数在单个分区内计算进一步减少扫描数据量。5.3 NULL、类型转换与脏数据现金日记账最怕脏数据。常见问题有两个一是amount字段为NULL。如果某笔记录的amount是NULLsum()会跳过它余额不发生变化这在财务看起来就像“这笔金额凭空消失了”。解决方案是在建表时加not null约束或者在SQL里用nvl(amount, 0)兜底。二是方向字段的大小写和取值混乱。有人存IN有人存in还有人存1/0。如果应用层没有统一编码SQL里的case when direction IN就会漏掉数据。强烈建议在业务接口层做校验同时SQL里可以写成upper(direction) IN增加容错。三是日期和金额的类型转换。Oracle里如果biz_date存的是字符串排序会变成字典序比如2025-1-8会排在2025-1-6前面还是后面完全看字符顺序结果可能和你想的完全不同。把日期字段改成真正的date类型是做财务SQL的第一条底线。提示在写窗口函数SQL之前先跑一条select count(*), count(distinct biz_date || | || record_id) from cash_ledger where ...看看排序键的重复率。如果count(*)和count(distinct ...)不一致说明排序键不唯一余额一定会有问题。这条检查只要几秒钟能帮你省下几小时的排查时间。5.4 可以继续扩展的方向窗口函数解决累计求和之后现金日记账还可以延伸出不少实用场景月末余额试算平衡累加当月全部收入和支出再和总账模块核对。每日结存报表用partition by biz_date分组输出每天最后一笔的余额。异常波动告警用lag()函数取上一笔余额和当前余额做差如果某天支出异常放大可以自动标记。结合流水号做流水续接把跨月记录用row_number()统一编号保证展示顺序稳定。窗口函数是财务SQL里的“万金油”sum()做累计、lag()做环比、row_number()做编号掌握这几个基本就够应对绝大多数报表需求。我在实际项目里用过很多次这套写法从Oracle到SQL Server再到MySQL 8.0都跑过。最大的体会就是不要把rownum当成一个固定的行号来用它只是在特定查询上下文里临时分配的伪列。先排序、再编号、后累计这三个动作顺序不能乱。如果你刚开始接触窗口函数建议把第3.2节的SQL亲手跑一遍然后把order by running_seq删掉再看一眼结果——你会立刻明白over()里有没有order by的差别有多大。理解了这一步后面再复杂的累计报表都不难拿捏。 窗口函数实现现金日记账：sum() over(order by rownum)避坑指南
做财务系统的朋友应该都有体会现金日记账这种功能看着简单真要把它写清楚还挺折腾。业务上无非是“每发生一笔收支余额等于上一笔余额加收入减支出”可这个“上一笔余额”落到SQL里就成了一道典型的累计求和题。我最早是用游标循环一行行算的数据量小还凑合流水一多性能直接拉胯。后来换成sum() over()窗口函数SQL精炼了不少但真正让我栽跟头的是对排序的理解——这也是今天这篇要聊的核心用sum() over(order by rownum)实现现金日记账以及在Oracle里关于rownum的那些坑。这篇文章适合三类人看一是被财务模块累计余额折磨过的后端开发二是刚接触窗口函数、想弄明白over(order by ...)到底怎么累加的SQL新手三是从Oracle往其他数据库迁移时被rownum的差异坑过的朋友。我会把业务模型、SQL原理、完整可跑的案例、还有不同数据库的改写方式都拆开讲清楚。1. 现金日记账的业务模型与SQL思路拆解1.1 现金日记账到底在算什么现金日记账是财务模块里最基础的账簿之一用来逐笔记录企业现金的收入、支出并实时给出余额。它的规则非常固定按业务发生的时间顺序每登记一笔就用上一笔的余额加上本笔收入、减去本笔支出得到最新余额。举个例子一张最基础的现金流水表可能是这样的凭证号日期摘要方向金额0012025-01-06期初余额收入10000.000022025-01-06购买办公用品支出500.000032025-01-07销售货款收入8000.000042025-01-07差旅费报销支出1200.000052025-01-07业务招待费支出1200.00余额的计算逻辑是这样的第一笔后面的余额是10000.00第二笔余额变成10000.00减500.00等于9500.00第三笔变成9500.00加8000.00等于17500.00。每一行的余额都依赖前面所有行的累计结果这天然就是一个“滚动求和”的问题。把这个问题翻译成SQL语言你会发现自己面对的是一个非常典型的场景每一行输出的值不能只由当前行决定还要参考“本行之前所有行”的数据。这跟group by做的分组汇总完全是两码事——分组汇总会把很多行合并成一行而日记账要求每一行都保留同时带上截至当前行的累计值。1.2 为什么线性累加不能靠一组简单聚合完成很多初学者第一反应是那我用子查询把之前所有金额加起来不就行了比如select a.biz_date, a.amount, (select sum(b.amount) from cash_ledger b where b.biz_date a.biz_date or (b.biz_date a.biz_date and b.record_id a.record_id) ) as running_total from cash_ledger a;这个写法逻辑上没错但性能上有大问题。假设表里有N行数据每一行都要去扫描一次前面的数据整体复杂度接近O(N²)。当流水达到几万、几十万条时这条SQL能把数据库拖到怀疑人生。自连接也能做但写法更啰嗦而且同样面临性能瓶颈。现金日记账往往是财务每天都要打开看的页面不可能让用户等几十秒才出结果。窗口函数sum() over()解决的正是这个问题。它可以在一次扫描的过程中把“截至当前行的累计值”算出来不需要反复回表也不需要自己拼子查询。这也是窗口函数名字的由来它开了一个“窗口”这个窗口随当前行移动窗口内是当前行之前的所有行然后在这个窗口上做聚合。1.3 为什么排序是日记账SQL的命门窗口函数本身不关心业务顺序它只认over()里面的order by。所以“排序键”选什么直接决定累计结果对不对。现金日记账的业务顺序说到底就是“时间顺序”。但实际表里往往没有“自然顺序”字段只有一个自增主键比如record_id。有经验的做法是用biz_date加record_id来做排序键。可这里有个隐藏问题——在多笔业务发生在同一天时只有日期没法确定先后就算加了record_id如果未来有数据回补、批量导入主键顺序也不一定等于业务顺序。这时候就需要一个“稳定的、排好序的行号”来当代理排序键。Oracle里的rownum就是干这个用的但它有一个非常大的坑我会在下一节专门讲。搞懂了排序这个SQL就成功了一半。2. sum() over() 的排序逻辑与 rownum 的关键坑2.1 SUM(...) OVER(ORDER BY ...) 的执行过程要理解sum() over()先记住一个结论没有order by的窗口函数窗口是整张表有order by的窗口函数窗口是从第一行到当前行的累计范围。看这个最简单的例子select record_id, amount, sum(amount) over(order by record_id) as running_total from cash_ledger;sum(amount)本身是求和over(order by record_id)告诉数据库“你要按record_id从小到大排序然后对每一行都对它前面的所有行做累加”。所以running_total这一列就是逐行累加的结果。执行顺序上窗口函数是在from、where、group by、having之后执行的。也就是说先选出符合条件的行再对这些行排序并开窗累加。这也意味着窗口函数不能引用同一层级中另一个窗口函数的别名得套一层子查询才能复用。用生活化的类比理解想象你手拿一叠纸质报销单按日期排好序然后拿计算器从第一张开始一张一张往下加。你每加完一张记下的当前总数就是sum() over(order by ...)给那一行的结果。窗口函数帮你自动完成了“一边移动、一边累加”的过程。2.2 排序键不唯一的稳定顺序陷阱很多人在这一步开始踩坑。比如只按biz_date排序select left(record_id, 3) as voucher_no, biz_date, amount, sum(amount) over(order by biz_date) as running_total from cash_ledger;如果同一天有多笔业务比如上面示例里1月7日的两笔支出数据库到底先加1200还是后加1200答案是不确定。当order by的排序键存在重复值时数据库可能按物理存储顺序、也可能按索引顺序、甚至并行执行时结果都不同。也就是说同样的数据跑两次余额可能不一样。这在财务系统里是绝对不允许的。日记账的余额必须精确到每一笔不能出现“相同的流水、不同的余额”这种低级错误。解决办法很直接排序键必须能唯一定位每一行。要么用主键record_id要么用“日期凭证号行号”这类复合键。如果你要保留严格的业务顺序最稳妥的方案是先按业务规则排好序再生成一个连续且唯一的行号最后拿这个行号当窗口函数的排序键。这就是下面要讲的rownum方案。2.3 Oracle ROWNUM 的“先编号后排序”问题Oracle 里有个非常容易误解的伪列叫rownum。很多新手以为它是“物理行号”以为表里的数据固定有一个rownum。事实完全不是这样。rownum是查询结果集生成过程中分配的伪列——每返回一行rownum就加一。它的分配时机是在“行被取出来”的时候而不是在“最终排序完成”的时候。所以如果你写select record_id, biz_date, amount, rownum from cash_ledger order by biz_date desc;你会看到rownum仍然是按原表扫描顺序编号的1、2、3并不会因为order by biz_date desc而重新排序。这个特性坑过无数人。在现金日记账里我们真正想要的是先把数据按日期、凭证号排好再给它们安上连续的序号1、2、3……随后基于这个序号做累计。正确的写法是“三层嵌套”最内层负责业务排序中间层生成固定rownum最外层做sum() over(order by rownum)。如果省略中间层直接在外层写order by rownum那rownum仍然是原始扫描顺序的编号跟业务顺序没有关系余额自然就是错的。这就是很多人在网上搜到这个写法却跑出错误结果的根本原因。3. 实操用 sum() over(order by rownum) 实现现金日记账3.1 准备测试表和数据纸上谈兵没有意义直接来一套可以跑的Oracle示例。先建表create table cash_ledger ( record_id number(10) primary key, biz_date date not null, direction varchar2(2) not null, -- IN 收入 / OUT 支出 amount number(12,2) not null, summary varchar2(200) );插入测试数据为了体现“同一天多笔”的场景1月7日特意放了两笔支出insert into cash_ledger values (1, date 2025-01-06, IN, 10000.00, 期初余额); insert into cash_ledger values (2, date 2025-01-06, OUT, 500.00, 购买办公用品); insert into cash_ledger values (3, date 2025-01-07, IN, 8000.00, 销售货款); insert into cash_ledger values (4, date 2025-01-07, OUT, 1200.00, 差旅费报销); insert into cash_ledger values (5, date 2025-01-07, OUT, 1200.00, 业务招待费); insert into cash_ledger values (6, date 2025-01-08, IN, 2000.00, 其他收入); commit;这里的record_id在实际项目中可能是序列生成的也可能是一张流水凭证表的自增主键。我特意让它均匀递增但你要注意业务顺序并不总是等于主键顺序。更真实的情况是凭证可能跨月补录导致record_id大但日期小。所以在排序时order by biz_date, record_id才是符合业务直觉的做法。3.2 核心SQL三层嵌套写法完整的现金日记账SQL长这样select biz_date, direction, amount, summary, running_seq, nvl( sum( case when direction IN then amount when direction OUT then -amount else 0 end ) over (order by running_seq), 0 ) as balance from ( select record_id, biz_date, direction, amount, summary, rownum as running_seq from ( select record_id, biz_date, direction, amount, summary from cash_ledger order by biz_date, record_id ) ) order by running_seq;执行结果如下biz_datedirectionamountsummaryrunning_seqbalance2025-01-06IN10000.00期初余额110000.002025-01-06OUT500.00购买办公用品29500.002025-01-07IN8000.00销售货款317500.002025-01-07OUT1200.00差旅费报销416300.002025-01-07OUT1200.00业务招待费515100.002025-01-08IN2000.00其他收入617100.00这个结果和手工计算的余额完全一致。重点看中间层先按biz_date, record_id排序再通过rownum生成连续编号running_seq。最外层的sum() over(order by running_seq)拿到的是一个“干净”的、没有任何重复值的排序键累计顺序完全确定。提示running_seq这个中间层字段不一定非要叫这个名可以用任何你喜欢的别名。但强烈建议不要直接写rownum作为最终展示字段因为rownum在不同查询上下文中含义不一样很容易让人误解。3.3 方向金额转换与期初余额处理在这个SQL里case when direction IN then amount when direction OUT then -amount把收、支方向转成了带符号金额。这样做的目的是让sum()可以直接做“收入加、支出减”的运算不需要额外再写复杂逻辑。这里有个细节值得注意nvl(..., 0)不是可有可无的。如果表里没有任何数据或者某一行之前的累计值为NULL窗口函数可能会返回NULL给前端渲染带来麻烦。虽然正常情况下第一行就会累出正数但加上nvl能让输出更稳健也方便后续把余额字段直接用于展示。关于期初余额有两条经验最简单是把期初余额也做成一条direction IN的记录如本例所示。这样所有业务统一处理逻辑最简单。如果期初余额是单独存在参数表里的不体现在流水里那么可以先把期初余额作为变量传入再把累计结果加上这个变量select biz_date, direction, amount, summary, nvl(sum(signed_amount) over (order by running_seq), 0) :initial_balance as balance from (...);第一种方案更适合大多数财务系统因为期初余额本身也要出现在日记账首页供核对做成记录不丢明细。3.4 扩展按日结账与跨天余额现金日记账还有一个常见需求每天下班前看一个“日结余额”。用窗口函数也能轻松做到只需要在over()里加一个partition by biz_dateselect biz_date, direction, amount, summary, sum( case when direction IN then amount when direction OUT then -amount else 0 end ) over (partition by biz_date order by running_seq) as daily_balance from (...);这里的partition by biz_date会把同一天的数据单独分组组内再按running_seq累计。这样每天的第一笔余额从0开始累加看不到上一日的结转数。如果你想要“每天累计到当天为止的总余额”还是用不带partition by的版本然后按日期取最大值即可。跨月结转也是财务系统的经典场景。推荐做法是每个会计期间开始时插入一笔期初余额记录查询时where biz_date 期间开始日期这样窗口函数自然从期初开始累加不需要额外写结转逻辑。4. 不同数据库下的实现差异4.1 OracleROWNUM 的经典写法Oracle里rownum顺手但正如前面提到的必须先排序再编号。完整的Oracle写法就是第3.2节那套三层嵌套不再重复。这套写法在Oracle 11g、12c、19c上都没有问题。Oracle 18c之后也有了row_number()窗口函数如果你更习惯显式的行号函数可以用row_number() over(order by biz_date, record_id)替代中间层的rownum结果一样稳定而且可读性更好select biz_date, direction, amount, summary, sum( case when direction IN then amount when direction OUT then -amount else 0 end ) over (order by running_seq) as balance from ( select record_id, biz_date, direction, amount, summary, row_number() over (order by biz_date, record_id) as running_seq from cash_ledger ) order by running_seq;如果你还在维护老的Oracle系统建议优先用row_number()语义更清晰也方便将来迁移到其他数据库。4.2 SQL ServerROW_NUMBER() 替代SQL Server 没有rownum但它的row_number()是完整的窗口函数从2005版本开始就有了。日记账SQL可以直接这么写select biz_date, direction, amount, summary, sum( case when direction IN then amount when direction OUT then -amount else 0 end ) over (order by running_seq) as balance from ( select record_id, biz_date, direction, amount, summary, row_number() over (order by biz_date, record_id) as running_seq from cash_ledger ) t order by running_seq;注意SQL Server对表别名要求更严格子查询必须加别名比如这里的t。另外SQL Server中order by在子查询里通常不允许直接写除非配合offset或row_number()这类窗口函数使用上面的写法完全绕开了这个限制所以在实际项目中特别常用。4.3 MySQL 8.0 与其他数据库MySQL 在8.0版本之前没有窗口函数只能靠用户变量模拟累计求和写法又丑又容易踩坑-- 老版本MySQL用用户变量不推荐新项目使用 set seq : 0; set balance : 0; select biz_date, direction, amount, seq : seq 1 as running_seq, balance : balance case when direction IN then amount when direction OUT then -amount else 0 end as balance from cash_ledger order by biz_date, record_id;这种写法的balance依赖变量赋值顺序在复杂SQL和并发环境下容易出问题只建议用于临时查询。如果用的是MySQL 8.0写法跟SQL Server几乎一样select biz_date, direction, amount, summary, sum( case when direction IN then amount when direction OUT then -amount else 0 end ) over (order by running_seq) as balance from ( select record_id, biz_date, direction, amount, summary, row_number() over (order by biz_date, record_id) as running_seq from cash_ledger ) t order by running_seq;PostgreSQL、达梦等支持窗口函数的数据库大同小异。达梦因为兼容Oracle甚至连rownum的行为都很接近Oracle可以直接套用前面的Oracle三层结构。数据库行号方式窗口函数支持度推荐写法Oraclerownum / row_number()完整row_number() over(order by ...)SQL Serverrow_number()完整row_number() over(order by ...)MySQL 8.0row_number()完整row_number() over(order by ...)老版本MySQL用户变量不支持建议升级或用变量模拟达梦rownum / row_number()完整兼容Oracle写法5. 常见问题与性能调优实录5.1 余额错乱、顺序漂移怎么排查如果你发现余额算出来和手工核对不一致优先按这个顺序排查确认排序键有没有重复。order by biz_date在一天有多笔业务时是不安全的必须加上record_id或凭证号等唯一字段。检查有没有在排序键上做函数运算。比如order by trunc(biz_date)一旦把日期截断成天多笔同一天的数据又进入“同值”状态顺序漂移会再次出现。确认最内层的排序结果是否真的符合业务预期。如果存在“补录凭证”日期早但ID大要结合业务规则决定按什么排。财务上通常以凭证日期和凭证号为准。我曾经排查过一个线上问题用户看到某些天的余额偶发错乱尤其是月底月初交接时。最后发现是业务系统里存在“红字冲销单”金额为负数方向字段却仍是OUT。如果没有针对负数金额做特殊判断累计结果自然不对。排查时先把方向、金额、排序键全部列出来人工核对前20笔问题很快就暴露了。5.2 大表数据量下的性能优化现金日记账在年末可能有几十万甚至上百万条流水窗口函数的性能不可忽视。窗口函数比游标和自连接快得多因为它只需要一次全表扫描。但扫描是有代价的想让SQL跑得更快核心思路是缩小扫描范围并减少排序成本。加过滤条件查询日记账通常只查某个月或某个期间尽量加上where biz_date between ... and ...。建复合索引在(biz_date, record_id)上建索引能显著降低排序开销。如果查询还带其他筛选条件再考虑把这些筛选字段一起放进索引。避免最内层大量排序order by biz_date, record_id如果能走索引就不需要显式排序否则Oracle会在排序区操作数据量大时临时表空间会吃紧。不要在大表上直接用select *包多层子查询能在外层只取需要的字段就减少不必要的列参与排序。实际案例里把十几万条流水套上上面这套结构后查询从原来的2秒多优化到0.3秒以内效果非常明显。如果你的系统数据量更大还可以考虑按月份分区表让窗口函数在单个分区内计算进一步减少扫描数据量。5.3 NULL、类型转换与脏数据现金日记账最怕脏数据。常见问题有两个一是amount字段为NULL。如果某笔记录的amount是NULLsum()会跳过它余额不发生变化这在财务看起来就像“这笔金额凭空消失了”。解决方案是在建表时加not null约束或者在SQL里用nvl(amount, 0)兜底。二是方向字段的大小写和取值混乱。有人存IN有人存in还有人存1/0。如果应用层没有统一编码SQL里的case when direction IN就会漏掉数据。强烈建议在业务接口层做校验同时SQL里可以写成upper(direction) IN增加容错。三是日期和金额的类型转换。Oracle里如果biz_date存的是字符串排序会变成字典序比如2025-1-8会排在2025-1-6前面还是后面完全看字符顺序结果可能和你想的完全不同。把日期字段改成真正的date类型是做财务SQL的第一条底线。提示在写窗口函数SQL之前先跑一条select count(*), count(distinct biz_date || | || record_id) from cash_ledger where ...看看排序键的重复率。如果count(*)和count(distinct ...)不一致说明排序键不唯一余额一定会有问题。这条检查只要几秒钟能帮你省下几小时的排查时间。5.4 可以继续扩展的方向窗口函数解决累计求和之后现金日记账还可以延伸出不少实用场景月末余额试算平衡累加当月全部收入和支出再和总账模块核对。每日结存报表用partition by biz_date分组输出每天最后一笔的余额。异常波动告警用lag()函数取上一笔余额和当前余额做差如果某天支出异常放大可以自动标记。结合流水号做流水续接把跨月记录用row_number()统一编号保证展示顺序稳定。窗口函数是财务SQL里的“万金油”sum()做累计、lag()做环比、row_number()做编号掌握这几个基本就够应对绝大多数报表需求。我在实际项目里用过很多次这套写法从Oracle到SQL Server再到MySQL 8.0都跑过。最大的体会就是不要把rownum当成一个固定的行号来用它只是在特定查询上下文里临时分配的伪列。先排序、再编号、后累计这三个动作顺序不能乱。如果你刚开始接触窗口函数建议把第3.2节的SQL亲手跑一遍然后把order by running_seq删掉再看一眼结果——你会立刻明白over()里有没有order by的差别有多大。理解了这一步后面再复杂的累计报表都不难拿捏。