求一简单经典语句。

create table tb(F_1 varchar(10) , F_2 int)

insert into tb values('AA', 1 )

insert into tb values('BB', 3 )

insert into tb values('CC', 7 )

go



select tb.* from tb where f_1 in (select min(f_1) from tb)

union all

select t2.f_1 , f_2 = t2.f_2 - t1.f_2 from

(SELECT * , px = (SELECT COUNT(f_1) FROM tb WHERE f_1 < t.f_1) + 1 FROM tb t) t1,

(SELECT * , px = (SELECT COUNT(f_1) FROM tb WHERE f_1 < t.f_1) + 1 FROM tb t) t2

where t1.px = t2.px - 1



drop table tb



/*

F_1        F_2         

---------- ----------- 

AA         1

BB         2

CC         4



（所影响的行数为 3 行）

*/

declare @a table(F_1 varchar(10),F_2 int) 

insert into @a select 'AA',       1 

insert into @a select 'BB',       3 

insert into @a select 'CC',       7 





select id=identity(int,1,1),* into #temp from @a





select a.f_1,isnull(a.f_2-b.f_2,a.f_2)f_2 from #temp a left join #temp b

on a.id=b.id+1 

declare @tb table (f1 varchar(10),f2 int)

insert into @tb select 'aa',1

insert into @tb select 'bb',3

insert into @tb select 'cc',7

select a.f1,a.f2-isnull(b.f2,0) as '差' from (

select *, px=row_number() over(order by f1)from @tb )a

left join(

select *, px=row_number() over(order by f1)from @tb )b

on a.px=b.px+1