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
递归的方法我已经写出来了，请问下用关联的方法怎么写？
------解决方案--------------------
递归方法本事就是关联，如果你递归方法没写错，你可以得到想要的结果
------解决方案--------------------
递归就是迭代的关联前一条记录
看看我的文章
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
------解决方案--------------------