求一个sql递减的语句

[zx_llcs]

Id wage wagetime
A 3000 2014-07-08
A 300 2014-08-08
A 200 2014-09-08
B 3100 2014-07-08
B 310 2014-08-08
B 200 2014-09-08
C 3200 2014-07-08
C 320 2014-08-08
D 3300 2014-07-08
D 330 2014-08-08
E 3400 2014-07-08
E 340 2014-08-08

希望得到查询结果(是按照时间先后排序 来递减的)

Id wage wage1 wagetime
A 3000 3000 2014-07-08
A 300 2700 2014-08-08
A 200 2500 2014-09-08
B 3100 3100 2014-07-08
B 310 3000 2014-08-08
B 200 2800 2014-09-08
C 3300 3300 2014-07-08
C 320 2980 2014-08-08
D 3400 3400 2014-07-08
D 330 3070 2014-08-08
E 3500 3500 2014-07-08
E 340 3160 2014-08-08

递归的方法我已经写出来了，请问下用关联的方法怎么写？

[xdashewan]

递归方法本事就是关联，如果你递归方法没写错，你可以得到想要的结果

[KeepSayingNo]

递归就是迭代的关联前一条记录 看看我的文章 http://blog.csdn.net/dotnetstudio/article/details/10109497

[哥眼神纯洁不]

不用递归也行吧...

with tb(Id,wage,wagetime)as(
 select 'A',3000,'2014-07-08' union
 select 'A',300,'2014-08-08' union
 select 'A',200,'2014-09-08' union 
 select 'B',3100,'2014-07-08' union
 select 'B',310,'2014-08-08' union
 select 'B',200,'2014-09-08' union 
 select 'C',3200,'2014-07-08' union
 select 'C',320,'2014-08-08' union
 select 'D',3300,'2014-07-08' union 
 select 'D',330,'2014-08-08' union
 select 'E',3400,'2014-07-08' union
 select 'E',340,'2014-08-08')
 ,TC AS(
 SELECT *,CASE WHEN WAGETIME!=(SELECT MIN(WAGETIME) FROM tb WHERE A.Id=ID) 
 THEN wage*-1 ELSE wage END NEW FROM TB A
 )
 SELECT ID,wage,(SELECT SUM(NEW) FROM TC WHERE A.wagetime>=wagetime AND A.Id=ID)WAGE1,wagetime
 FROM TC A
 ORDER BY ID,3 DESC

[向东流]

;WITH CTE AS ( SELECT ROW_NUMBER()OVER (PARTITION BY ID ORDER BY WAGETIME) AS XH,* FROM tb ) SELECT ID,WAGE,CASE WHEN XH=1 THEN WAGE ELSE (SELECT WAGE FROM CTE B WHERE XH=1 AND A.ID=B.ID)-(SELECT SUM(C.WAGE) FROM CTE C WHERE C.XH<>1 AND C.XH<=A.XH AND C.ID=A.ID) END AS WAGE1,WAGETIME FROM CTE A

[-狙击手-]

; with cte(id,wage,wagetime,rid) as ( select *,rid = row_number() over(partition by id order by wagetime) from [ttt] ) , cte1(id,wage,wagetime,rid,tt) as (select ID ,wage,wagetime,rid,(select SUM(case when rid = 1 then wage else -1*wage end) from cte where id = a.id and rid <=a.rid) from cte a ) select * from cte1

[向东流]

一句的，看起来比较累

SELECT ID,WAGE,CASE WHEN XH=1 THEN WAGE
ELSE (SELECT WAGE FROM (SELECT ROW_NUMBER()OVER (PARTITION BY ID ORDER BY WAGETIME) AS XH,* FROM tb) B WHERE XH=1 AND A.ID=B.ID)-(SELECT SUM(C.WAGE) FROM (SELECT ROW_NUMBER()OVER (PARTITION BY ID ORDER BY WAGETIME) AS XH,* FROM tb) C WHERE C.XH<>1 AND C.XH<=A.XH AND C.ID=A.ID) END AS WAGE1,WAGETIME
FROM (SELECT ROW_NUMBER()OVER (PARTITION BY ID ORDER BY WAGETIME) AS XH,* FROM tb) A