求助一个SQL显示当前修改过的分数，并且还有原始记录的问题，在线等～～～～～～～～～～～～～

这是现在的表结构
updateuser score updatetime
0006 5 2012-08-25
0007 3 2012-08-26
0009 4 2012-08-27
0006 3 2012-08-28

现在想出这个效果

updateuser nowscore pastscore updatetime
0007 3 5 2012-08-26
0009 4 3 2012-08-27
0006 3 4 2012-08-28

哪位大哥知道，小弟谢过啦


[解决办法]

SQL code

 declare @T table(updateuser varchar(4), score int, updatetime datetime)insert into @Tselect '0006', 5, '2012-08-25' union allselect '0007', 3, '2012-08-26' union allselect '0009', 4, '2012-08-27' union allselect '0006', 3, '2012-08-28';with cte as(    select row_number() over(order by updatetime) rn,* from @T)select * from(    select updateuser,    score as nowscore,    pastscore=(select score from cte where t.rn=rn+1),    convert(varchar(10),updatetime,120) updatetime    from cte t) ttwhere tt.pastscore is not null/*updateuser nowscore    pastscore   updatetime---------- ----------- ----------- ----------0007       3           5           2012-08-260009       4           3           2012-08-270006       3           4           2012-08-28*/
[解决办法]
SQL code
USE tempdbGOCREATE TABLE test(    updateuser    VARCHAR(10),    score        INT,    updatetime    SMALLDATETIME);INSERT INTO testSELECT '0006',5,'2012-08-25'UNION ALL SELECT '0007',3,'2012-08-26'UNION ALL SELECT '0009',4,'2012-08-27'UNION ALL SELECT '0006',3,'2012-08-28'SELECT ROW_NUMBER()OVER (ORDER BY updatetime ) AS id ,* INTO #t FROM test /*updateuser nowscore pastscore    updatetime 0007        3        5            2012-08-26 0009        4        3            2012-08-27 0006        3        4            2012-08-28*/SELECT * FROM #tSELECT updateuser ,score AS nowscore,(SELECT b.score  FROM #t b WHERE a.id=b.id+1 ) AS pastcsroe,a.updatetimeFROM #t a WHERE (SELECT b.score  FROM #t b WHERE a.id=b.id+1 ) IS NOT NULL