sql语法请教，这一行某列的数是上一行该列，经过计算后得出来的数，语法怎么写

做资金日报表

就像上面的图  下面这行的昨日余额列等于上面这列加减对应的收入和支出列，或是取自上一行今日余额列，请问这种方式应该怎么写

--测试数据if not object_id(N'Tempdb..#T') is null    drop table #TGoCreate table #T([time] Date,[余额] int,[收入] int,[支出] int)Insert #Tselect '2017-05-01',10,5,8 union allselect '2017-05-02',0,50,28 union allselect '2017-05-03',0,15,18Go--测试数据结束;WITH cte AS (SELECT * FROM #T WHERE time=(SELECT MIN(time) FROM #T)UNION ALLSELECT  a.time ,        ( b.余额 + b.收入 - b.支出 ) AS 余额 ,        a.收入 ,        a.支出FROM    #T a        JOIN cte b ON DATEDIFF(DAY, b.time, a.time) = 1)SELECT * FROM cte

--根据上面示例和自己的实际需求，以下是实际的代码

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[银行存款日报表]') AND type in (N'U'))DROP TABLE [dbo].[银行存款日报表]--if not object_id(N'Tempdb..#T') is null--    drop table 银行存款日报表GoCreate table 银行存款日报表([日期] Date,[银行] nvarchar(50),[账户名称] nvarchar(50),[昨日余额] float,[今日收入] float,[今日支出] float,[今日余额] float,[收入笔数] int,[支出笔数] int)Insert 银行存款日报表----和兴鑫--------select b.日期,b.银行,b.账户名称,a.昨日余额,a.今日收入,a.今日支出,a.今日余额,a.收入笔数,a.支出笔数 from (

--添加日期列的代码select convert(varchar(10),dateadd(day,number,'2016-11-30'),120)  as 日期,'锦州银行' as 银行,'和兴鑫' as 账户名称from    master..spt_values where     datediff(day,dateadd(day,number,'2016-11-30'), GETDATE())>=0    and number>=0     and type='p')b

--添加日期列的代码
left join (select q.日期,q.昨日余额,sum(q.今日收入)as 今日收入,sum(q.今日支出) as 今日支出,q.今日余额,sum(q.收入笔数) as 收入笔数,sum(q.支出笔数)as 支出笔数 from (select CONVERT(varchar(100), isnull(t2.FDATE,'2016-11-30'), 23) as 日期,(case t3.FNUMBER when 01 then '工商银行' when 02 then '农业银行' when 03 then '建设银行' when 04 then '锦州银行' when 05 then '招商银行' else '' end) as 银行, t1.FNUMBER as 账户名称,isnull(t2.FBANKAMOUNT,0) as 昨日余额 ,null as 今日收入 ,null as 今日支出 ,null as 今日余额 ,null as 收入笔数,null as 支出笔数 from T_CN_BANKACNT t1 left join (select t1.fdate,t2.FBANKAMOUNT,t2.FBANKACNTID from T_CN_BANKACNTBALINIT t1 inner join T_CN_BANKACNTBALINITENTRY t2 on t1.FID=t2.FID) t2 on t1.FBANKACNTID=t2.FBANKACNTID left join T_BD_BANK t3 on t1.FBANKID=t3.FBANKIDunion all--收款单select CONVERT(varchar(100), t1.fdate, 23) as 日期, (case t4.FNUMBER when 01 then '工商银行' when 02 then '农业银行' when 03 then '建设银行' when 04 then '锦州银行' when 05 then '招商银行' else '' end) as 银行, t3.FNUMBER as 账户名称, null as 昨日余额, --(case when t2.FSETTLETYPEID=1 then '现金' when t2.FSETTLETYPEID=4 then '电汇' when t2.FSETTLETYPEID=2 then '现金支票' when t2.FSETTLETYPEID=3 then '转账支票' else '' end ) as 结算方式,sum(t2.FREALRECAMOUNTFOR) as 今日收入, null as 今日支出,null as 今日余额,COUNT(t2.FREALRECAMOUNTFOR) as 收入笔数 ,null as 支出笔数 from T_AR_RECEIVEBILL t1 inner join T_AR_RECEIVEBILLENTRY t2 on t1.FID=t2.FIDleft join T_CN_BANKACNT t3 on t2.FACCOUNTID=t3.FBANKACNTID left join T_BD_BANK t4 on t4.FBANKID=t3.FBANKIDwhere t2.FSETTLETYPEID in (3,4) and t1.FISINIT=0GROUP BY t1.fdate,t3.FNUMBER,t4.FNUMBERunion all--付款单select CONVERT(varchar(100), t1.fdate, 23) as 日期,(case t4.FNUMBER when 01 then '工商银行' when 02 then '农业银行' when 03 then '建设银行' when 04 then '锦州银行' when 05 then '招商银行' else '' end) as 银行, t3.FNUMBER as 账户名称,null as 昨日余额,--(case when t2.FSETTLETYPEID=1 then '现金' when t2.FSETTLETYPEID=4 then '电汇' when t2.FSETTLETYPEID=2 then '现金支票' when t2.FSETTLETYPEID=3 then '转账支票' else '' end ) as 结算方式 ,null as 今日收入,sum(t2.FREALPAYAMOUNTFOR) as 今日支出,null as 今日余额, null as 收入笔数,COUNT(t2.FREALPAYAMOUNTFOR) as 支出笔数 from T_AP_PAYBILL t1 inner join T_AP_PAYBILLENTRY t2 on t1.FID=t2.FID left join T_CN_BANKACNT t3 on t2.FACCOUNTID=t3.FBANKACNTID left join T_BD_BANK t4 on t4.FBANKID=t3.FBANKIDwhere t2.FSETTLETYPEID in (3,4)GROUP BY t1.fdate,t3.FNUMBER,t4.FNUMBERunion all--现金存取单 取款select CONVERT(varchar(100), t1.fdate, 23) as 日期,(case t4.FNUMBER when 01 then '工商银行' when 02 then '农业银行' when 03 then '建设银行' when 04 then '锦州银行' when 05 then '招商银行' else '' end) as 银行, t3.FNUMBER as 账户名称, null as 昨日余额,--(case when t1.FBILLTYPEID='361a64af5c004111962018af7d2762d0' then '取款' when t1.FBILLTYPEID='0388a5d89593462086f5ee63a21d973f' then '存款' else '' end) as 单据类型 ,null as 今日收入,SUM((case when t1.FBILLTYPEID='361a64af5c004111962018af7d2762d0' then FREALPAYAMOUNTFOR else '' end)) as 今日支出,null as 今日余额, null as 收入笔数,COUNT(t2.FREALPAYAMOUNTFOR) as 支出笔数 from T_CN_CASHACCESSBILL t1 inner join T_CN_CASHACCESSBILLENTRY t2 on t1.FID=t2.FID and t1.FBILLTYPEID='361a64af5c004111962018af7d2762d0' left join T_CN_BANKACNT t3 on t2.FBANKACNTID=t3.FBANKACNTID left join T_BD_BANK t4 on t4.FBANKID=t3.FBANKIDGROUP BY t1.fdate,t3.FNUMBER,t4.FNUMBERunion all--现金存取单 存款select CONVERT(varchar(100), t1.fdate, 23) as 日期,(case t4.FNUMBER when 01 then '工商银行' when 02 then '农业银行' when 03 then '建设银行' when 04 then '锦州银行' when 05 then '招商银行' else '' end) as 银行, t3.FNUMBER as 账户名称, null as 昨日余额,--(case when t1.FBILLTYPEID='361a64af5c004111962018af7d2762d0' then '取款' when t1.FBILLTYPEID='0388a5d89593462086f5ee63a21d973f' then '存款' else '' end) as 单据类型 ,SUM((case when t1.FBILLTYPEID='0388a5d89593462086f5ee63a21d973f' then FREALPAYAMOUNTFOR else '' end)) as 今日收入,null as 今日支出,null as 今日余额, COUNT( t2.FREALPAYAMOUNTFOR ) as 收入笔数,null as 支出笔数 from T_CN_CASHACCESSBILL t1 inner join T_CN_CASHACCESSBILLENTRY t2 on t1.FID=t2.FID and t1.FBILLTYPEID='0388a5d89593462086f5ee63a21d973f' left join T_CN_BANKACNT t3 on t2.FBANKACNTID=t3.FBANKACNTID left join T_BD_BANK t4 on t4.FBANKID=t3.FBANKIDGROUP BY t1.fdate,t3.FNUMBER ,t4.FNUMBERunion allselect CONVERT(varchar(100), t1.fdate, 23) as 日期,(case t4.FNUMBER when 01 then '工商银行' when 02 then '农业银行' when 03 then '建设银行' when 04 then '锦州银行' when 05 then '招商银行' else '' end) as 银行, t3.FNUMBER as 账户名称, null as 昨日余额,sum(t2.FAMOUNT) as 今日收入,null as 今日支出,null as 今日余额, COUNT( t2.FAMOUNT ) as 收入笔数,null as 支出笔数 from T_CN_BANKTRANSBILL t1 inner join T_CN_BANKTRANSBILLENTRY t2 on t1.FID=t2.FIDleft join T_CN_BANKACNT t3 on t2.FTOBANKACNTID=t3.FBANKACNTID left join T_BD_BANK t4 on t4.FBANKID=t2.FTOBANKIDleft join T_CN_BANKACNT t5 on t2.FFROMBANKACNTID=t5.FBANKACNTID left join T_BD_BANK t6 on t6.FBANKID=t2.FFROMBANKIDGROUP BY t1.fdate,t3.FNUMBER,t4.FNUMBERunion allselect CONVERT(varchar(100), t1.fdate, 23) as 日期,(case t6.FNUMBER when 01 then '工商银行' when 02 then '农业银行' when 03 then '建设银行' when 04 then '锦州银行' when 05 then '招商银行' else '' end) as 银行, t5.FNUMBER as 账户名称,null as 昨日余额,null as 今日收入,sum(t2.FTOTALAMOUNT) as 今日支出,null as 今日余额,null as 收入笔数,COUNT(t2.FTOTALAMOUNT) as 支出笔数 from T_CN_BANKTRANSBILL t1 inner join T_CN_BANKTRANSBILLENTRY t2 on t1.FID=t2.FIDleft join T_CN_BANKACNT t3 on t2.FTOBANKACNTID=t3.FBANKACNTID left join T_BD_BANK t4 on t4.FBANKID=t2.FTOBANKIDleft join T_CN_BANKACNT t5 on t2.FFROMBANKACNTID=t5.FBANKACNTID left join T_BD_BANK t6 on t6.FBANKID=t2.FFROMBANKIDGROUP BY t1.fdate,t5.FNUMBER,t6.FNUMBER) q where q.账户名称='和兴鑫'GROUP BY q.日期,q.银行,q.账户名称,q.昨日余额,q.今日余额
) a on a.日期=b.日期GROUP BY b.日期,b.银行,b.账户名称,a.昨日余额,a.今日收入,a.今日支出,a.今日余额,a.收入笔数,a.支出笔数----和兴鑫--------go;WITH a (日期,银行,账户名称,昨日余额,今日收入,今日支出,今日余额,收入笔数,支出笔数)AS (    SELECT 日期,银行,账户名称,[昨日余额],isnull([今日收入],0),isnull([今日支出],0),([昨日余额]+isnull([今日收入],0)-isnull([今日支出],0)) AS [今日余额],收入笔数,支出笔数    FROM 银行存款日报表    WHERE 日期=(SELECT MIN(日期) FROM 银行存款日报表)    UNION ALL    SELECT 银行存款日报表.日期,银行存款日报表.银行,银行存款日报表.账户名称,a.[今日余额] AS [昨日余额],isnull(银行存款日报表.[今日收入],0),isnull(银行存款日报表.[今日支出],0),(a.[今日余额]+isnull(银行存款日报表.[今日收入],0)-isnull(银行存款日报表.[今日支出],0)) AS [今日余额],银行存款日报表.收入笔数,银行存款日报表.支出笔数    FROM 银行存款日报表    INNER JOIN a ON dateadd(d,1,a.日期) = 银行存款日报表.日期 and a.银行=银行存款日报表.银行 and a.账户名称=银行存款日报表.账户名称)SELECT * FROM a  order by a.日期,a.银行  OPTION (MAXRECURSION 0);