select * ,identity(int,1,1) as aa into #aa from View_Test select a.no,a.date,a.income,a.payout,datediff(d,a.date,isnull(b.date,getdate())) as PersistDays , (case when a.aa=1 then dbo.GetStartMoney()-a.Payout else ( case when b.income is null then a.Balance- b.payout else a.Balance+ b.income end) end) as Balance from #aa a left outer join #aa b on a.aa+1=b.aa建议做成存储过程,想不出来更好的办法
select a.No,a.Date.a,Income,a.Payout, datediff(day,a.date,isnull(b.date,getdate())) as PersistDays, dbo.GetStartMoney()+(select sum(isnull(Income,0)-isnull(Payout,0)) from View_Test where date<=a.date) as Balance from View_Test a left join View_Test b on b.Date=(select min(Date) from View_Test where Date>a.Date) --没测试,也许效率很不怎么样
try: 1、SELECT IDENTITY(int,1,1) AS [id],* INTO #T1 FROM View_Test2、A.[ID],A.[No],A.[Date],A.Income,A.Payout,DATEDIFF(day,A.[Date],B.[Date]) AS PersistDays,A.Balance FROM #T1 AS A LEFT OUTER JOIN #T1 AS B ON A.[id]+1=B.[id]3、 DECLARE @Balance int --Update用 --先Update第一行,再Update别的行 UPDATE #T1 SET Balance=dbo.GetStartMoney()-Payout WHERE [id]=1 SET @Balance=0 UPDATE #T1 SET @Balance=@Balance+ISNULL(Income,0)-ISNULL(Payout,0),Balance=@Balance WHERE [id]>04、 SELECT * FROM #T5、 --删除临时表 DROP TABLE #T,#T1基本的方法就这样。可以解决 。
写错了 临时表名,应该是:1、SELECT IDENTITY(int,1,1) AS [id],* INTO #T1 FROM View_Test2、A.[ID],A.[No],A.[Date],A.Income,A.Payout,DATEDIFF(day,A.[Date],B.[Date]) AS PersistDays,A.Balance INTO #T2 FROM #T1 AS A LEFT OUTER JOIN #T1 AS B ON A.[id]+1=B.[id]3、 DECLARE @Balance int --Update用 --先Update第一行,再Update别的行 UPDATE #T2 SET Balance=dbo.GetStartMoney()-Payout WHERE [id]=1 SET @Balance=0 UPDATE #T2 SET @Balance=@Balance+ISNULL(Income,0)-ISNULL(Payout,0),Balance=@Balance WHERE [id]>04、 SELECT * FROM #T25、 --删除临时表 DROP TABLE #T1,#T2
into #aa
from View_Test select a.no,a.date,a.income,a.payout,datediff(d,a.date,isnull(b.date,getdate())) as PersistDays ,
(case when a.aa=1 then dbo.GetStartMoney()-a.Payout else ( case when b.income is null then a.Balance- b.payout else a.Balance+ b.income end) end)
as Balance
from #aa a left outer join #aa b
on a.aa+1=b.aa建议做成存储过程,想不出来更好的办法
a.No,a.Date.a,Income,a.Payout,
datediff(day,a.date,isnull(b.date,getdate())) as PersistDays,
dbo.GetStartMoney()+(select sum(isnull(Income,0)-isnull(Payout,0)) from View_Test where date<=a.date) as Balance
from
View_Test a
left join
View_Test b
on b.Date=(select min(Date) from View_Test where Date>a.Date)
--没测试,也许效率很不怎么样
1、SELECT IDENTITY(int,1,1) AS [id],* INTO #T1 FROM View_Test2、A.[ID],A.[No],A.[Date],A.Income,A.Payout,DATEDIFF(day,A.[Date],B.[Date]) AS PersistDays,A.Balance
FROM #T1 AS A LEFT OUTER JOIN #T1 AS B ON A.[id]+1=B.[id]3、
DECLARE @Balance int --Update用
--先Update第一行,再Update别的行
UPDATE #T1 SET Balance=dbo.GetStartMoney()-Payout WHERE [id]=1
SET @Balance=0
UPDATE #T1 SET @Balance=@Balance+ISNULL(Income,0)-ISNULL(Payout,0),Balance=@Balance
WHERE [id]>04、
SELECT * FROM #T5、
--删除临时表
DROP TABLE #T,#T1基本的方法就这样。可以解决 。
INTO #T2
FROM #T1 AS A LEFT OUTER JOIN #T1 AS B ON A.[id]+1=B.[id]3、
DECLARE @Balance int --Update用
--先Update第一行,再Update别的行
UPDATE #T2 SET Balance=dbo.GetStartMoney()-Payout WHERE [id]=1
SET @Balance=0
UPDATE #T2 SET @Balance=@Balance+ISNULL(Income,0)-ISNULL(Payout,0),Balance=@Balance
WHERE [id]>04、
SELECT * FROM #T25、
--删除临时表
DROP TABLE #T1,#T2
表或者视图是已知的,结构如下
----------------------------------------
No | Date | Income | Payout |
----------------------------------------
NULL | 2001-02-15 | NULL | 360 |
-----------------------------------------
12 | 2001-05-10 | 5000 | NULL |
----------------------------------------
NULL | 2002-01-01 | 2000 | NULL |
----------------------------------------
3 | 2002-02-15 | NULL | 500 |
-------------------------------------------现在要弄出这个新的临时表
----------------------------------------------------------------
No | Date | Income | Payout | PersistDays | Balance |
-----------------------------------------------------------------
NULL | 2001-02-15 | NULL | 360 | <天数之差1> | <余额1> |
-----------------------------------------------------------------
12 | 2001-05-10 | 5000 | NULL | <天数之差2> | <余额2> |
------------------------------------------------------------------
NULL | 2002-01-01 | 2000 | NULL | <天数之差3> | <余额3> |
-------------------------------------------------------------------
3 | 2002-02-15 | NULL | 500 | <天数之差4> | <余额4> |
------------------------------------------------------------------谢谢了!~