请教一个sql语句，求财务余额！

有一个财务银行账表BANK_ACCOUNT:
结构如下
ID(主键),     DATE（日期),     SERIAL_NUMBER(当天序号),      INCOME（收入），    PAY（支出）

我想按照（日期和当天序号）的排序方式（ORDER BY DATE ASC,SERIAL_NUMBER ASC）输出以下内容:
*************************************************************************************************************************
ID(主键),    DATE（日期),   SERIAL_NUMBER(当天序号),   INCOME（收入）， PAY（支出）,  BALANCE(余额)
*************************************************************************************************************************
余额计算公式：
   当前记录余额=上条记录的余额+当前记录收入-当前记录支出
  

10 个解决方案

#1

 with BANK_ACCOUNT(ID,DATE,SERIAL_NUMBER,INCOME,PAY) as (
 select 101,20151206,2,800,900 union 
 select 102,20151206,1,1000,400 union
 select 103,20151206,3,600,400 union
 select 104,20151207,2,300,50 union
 select 105,20151207,1,200,50 
 ),cte as(
  select ROW_NUMBER()over(order by DATE ASC,SERIAL_NUMBER ASC) as row,ID,DATE,SERIAL_NUMBER,INCOME,PAY from BANK_ACCOUNT)

表结构这样，后面不会写 了

#2

个人建议应该修改这个表的表设计, 添加余额字段, 每写入一笔交易记录时, 自动计算并填入余额字段值. 
而不是显示时才去计算得到的. 

#3

SELECT 
	*
,(SELECT SUM(ISNULL(INCOME,0)-ISNULL(PAY,0)) FROM TabName WHERE  DATE<a.DATE OR DATE=a.DATE AND SERIAL_NUMBER<=SERIAL_NUMBER) AS BALANCE
FROM TabName AS a

 

#4

NUMBER<=a.SERIAL_NUMBER

SELECT 
	*
,(SELECT SUM(ISNULL(INCOME,0)-ISNULL(PAY,0)) FROM TabName WHERE  DATE<a.DATE OR DATE=a.DATE AND SERIAL_NUMBER<=a.SERIAL_NUMBER) AS BALANCE
FROM TabName AS a

 

#5

 with BANK_ACCOUNT(ID,DATE,SERIAL_NUMBER,INCOME,PAY) as (
 select 101,20151206,2,800,900 union 
 select 102,20151206,1,1000,400 union
 select 103,20151206,3,600,400 union
 select 104,20151207,2,300,50 union
 select 105,20151207,1,200,50 
 ),cte as(
  select ROW_NUMBER()over(order by DATE ASC,SERIAL_NUMBER ASC) as row,ID,DATE,SERIAL_NUMBER,INCOME,PAY from BANK_ACCOUNT)
select cte.*,
(select sum(isnull(INCOME,0))-sum(isnull(PAY,0)) from cte t2 where cte.row>=t2.row) as BALANCE
 from cte order by DATE ASC,SERIAL_NUMBER ASC

搞定

#6

添加余额字段是不可能的，因为当你修改收入/支出时，你会更新全部数据。
当你插入记录时，你不可能保证要插入的记录日期永远比前面记录的日期要大，这时候你也要更新全部数据。

#7

谢谢2楼,经测试没问题.
现在我担心的是这样查询速度会不会很慢,我打算插入10万条记录测试运行速度.
再次感谢你

#8

如果你价格字段，存储你这里所谓上一条记录的ID，然后读取时用Left Join就可以了。不知道这样对效率是否有所改进。
就是这个意思：

with BANK_ACCOUNT(ID,DATE,SERIAL_NUMBER,INCOME,PAY,BALANCE,LASTID) as (
 select 101,20151206,2,800,900,500,102 union 
 select 102,20151206,1,1000,400,600,NULL union
 select 103,20151206,3,600,400,700,101 union
 select 104,20151207,2,300,50,1100,105 union
 select 105,20151207,1,200,50,850,103 
 )
SELECT isnull(lastline.BALANCE,0)+currentline.INCOME-currentline.PAY AS Calculated,
 * FROM BANK_ACCOUNT AS currentline LEFT JOIN BANK_ACCOUNT AS lastline ON currentline.LASTID=lastline.ID
 ORDER BY currentline.DATE,currentline.SERIAL_NUMBER

实际表不需要Balance字段，这里我仅是为了和计算值对比

#9

不符合实际业务，不可以随意变更明细次序的。
你产生每条明细的时候，真正的账户金额会进行加减产生一个新的余额（实际上减之前要先检查原余额是否足够），把这个余额复制到明细中就行了。

#10

谢谢各位。2楼的方法可行，但效率太低。
1千条记录就会慢下来，1万条动不了，10万条崩溃。
郁闷，能用sql实现的功能不想用代码实现呀，后面我对sql查出来的表进行分页浏览，每页100条记录，总算看上去没这么慢