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

本文链接：https://blog.csdn.net/qq_36783809/article/details/74455646

做资金日报表

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

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([time] Date,[余额] int,[收入] int,[支出] int)
Insert #T
select '2017-05-01',10,5,8 union all
select '2017-05-02',0,50,28 union all
select '2017-05-03',0,15,18
Go
--测试数据结束
;WITH cte AS (
SELECT * FROM #T WHERE time=(SELECT MIN(time) FROM #T)
UNION ALL
SELECT  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 银行存款日报表
Go
Create 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);