SQL计算问题，请达人相助
有如下一个表tb
nian yue zhi1 zhi2
2011 10 50 30
2011 11 30 40
2011 12 20 50
2012 1 100 60
2012 2 200 70
2012 3 300 80
需要得到如下的结果：
nian yue zhi1 zhi2 jisuanzhi
2011 10 50 30 0 ((50-null)*30)
2011 11 30 40 -800 ((30-50)*40)
2011 12 20 50 -500 ((20-30)*50)
2012 1 100 60 4800 ((100-20)*60)
2012 2 200 70 7000 ((200-100)*70)
2012 3 300 80 8000 ((300-200)*80)
我的计算公式是：最新一个月的zhi1减去上个月的zhi1然后在乘以最新这个月的zhi2，就得到了jisuanzhi这个字段的结果。请达人相助，谢谢！！
[最优解释]
select *,zhi1-isnull(
(select zhi1 from tb where
datepart(yy,dateadd(mm,1,ltrim(nian)+right('00'+ltrim(yue),2)+'01'))=t.nian and
datepart(mm,dateadd(mm,1,ltrim(nian)+right('00'+ltrim(yue),2)+'01'))=t.yue ),0)*zhi2 as zzz
from tb t
[其他解释]
改进一下：
--CREATE TABLE huang(nian INT, yue INT, zhi1 INT , zhi2 int )
--INSERT INTO huang
--SELECT 2011, 10 , 50 , 30
--UNION ALL SELECT 2011, 11 , 30 , 40
--UNION ALL SELECT 2011, 12 , 20 , 50
--UNION ALL SELECT 2012, 1 , 100 , 60
--UNION ALL SELECT 2012, 2 , 200 , 70
--UNION ALL SELECT 2012, 3 , 300 , 80
--SELECT * FROM Huang
;WITH cte AS
(
SELECT ROW_NUMBER()OVER(ORDER BY nian,yue )id,*
FROM huang
),
cte2 AS
(
SELECT *,ISNULL((zhi1-NULL )*30 ,0) jisuanzhi
FROM cte
WHERE id=1
UNION ALL
SELECT b.id,b.nian ,b.yue,b.zhi1,b.zhi2,(b.zhi1-a.zhi1)*b.zhi2
FROM cte2 a INNER JOIN cte b ON a.id=b.id-1
)
SELECT nian , yue , zhi1 , zhi2 , jisuanzhi FROM cte2
/*
nian yue zhi1 zhi2 jisuanzhi
----------- ----------- ----------- ----------- -----------
2011 10 50 30 0
2011 11 30 40 -800
2011 12 20 50 -500
2012 1 100 60 4800
2012 2 200 70 7000
2012 3 300 80 8000
(6 行受影响)
*/
--CREATE TABLE huang(nian INT, yue INT, zhi1 INT , zhi2 int )
--INSERT INTO huang
--SELECT 2011, 10 , 50 , 30
--UNION ALL SELECT 2011, 11 , 30 , 40
--UNION ALL SELECT 2011, 12 , 20 , 50
--UNION ALL SELECT 2012, 1 , 100 , 60
--UNION ALL SELECT 2012, 2 , 200 , 70
--UNION ALL SELECT 2012, 3 , 300 , 80
--SELECT * FROM Huang
;WITH cte AS
(
SELECT ROW_NUMBER()OVER(ORDER BY nian,yue )id,*
FROM huang
),
cte2 AS
(
SELECT *,(zhi1-NULL )*30 jisuanzhi
FROM cte
WHERE id=1
UNION ALL
SELECT b.id,b.nian ,b.yue,b.zhi1,b.zhi2,(b.zhi1-a.zhi1)*b.zhi2
FROM cte2 a INNER JOIN cte b ON a.id=b.id-1
)
SELECT * FROM cte2
/*
id nian yue zhi1 zhi2 jisuanzhi
-------------------- ----------- ----------- ----------- ----------- -----------
1 2011 10 50 30 NULL
2 2011 11 30 40 -800
3 2011 12 20 50 -500
4 2012 1 100 60 4800
5 2012 2 200 70 7000
6 2012 3 300 80 8000
(6 行受影响)
*/
