22,207
社区成员
发帖
与我相关
我的任务
分享
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字段,这里我仅是为了和计算值对比
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
搞定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
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
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)
表结构这样,后面不会写 了