select f_1 , f_2 = (select min(f_2) from tb where f_2 > t.f_2) - f_2 from tb t
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+1f1 差 aa 1 bb 2 cc 4
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
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 ) goselect 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 - 1drop table tb/* F_1 F_2 ---------- ----------- AA 1 BB 2 CC 4(所影响的行数为 3 行) */
[code=SQL]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 ) goselect 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 - 1drop table tb/* F_1 F_2 ---------- ----------- AA 1 BB 2 CC 4(所影响的行数为 3 行) */ [/code]
--随手敲的,不排除手误 create table tb(F_1 varchar(8) , F_2 int) insert into tb values('AA', 1 ) insert into tb values('BB', 3 ) insert into tb values('CC', 7 ) goselect a.F_1, F_2=a.F_2-isnull(max(b.F_2),0) from tb a left join tb b on a.F_2>b.F_2 group by a.F_1, a.F_2drop table tb
declare @T table (F_1 varchar(10) , F_2 int) insert into @T values('AA', 1 ) insert into @T values('BB', 3 ) insert into @T values('CC', 7 )--根据F_1列判断 select F_1,F_2-isnull((select F_2 from @T where F_1=(select max(F_1) from @T where F_1<t.F_1)),0) as F_2 from @T t /*(所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行)F_1 F_2 ---------- ----------- AA 1 BB 2 CC 4(所影响的行数为 3 行) */---2005 select t.F_1,t.F_2-t2.F_2 as F_2 from (select *, row=row_number()over(order by F_1)from @T)T left join (select *, row=row_number()over(order by F_1)from @T)T2 on t.row=T2.row-1 -----------2000没有F_1\F_2没有大小关系时,只有通过生成临时表实现
select *, px=row_number() over(order by f1)from @tb )a 这一句怎么理解呢?
if object_id('tb') is not null drop table tb go create table tb (F_1 varchar(10),F_2 int) insert into tb select 'AA',1 union all select 'BB',3 union all select 'CC',7 declare @t table(id int identity(1,1),F_1 varchar(10),F_2 int) insert into @t select * from tb select top 1 F_1,F_2 from @t union all select a.F_1,b.F_2-a.F_2 as F_2 from @t a,@t b where a.id=b.id-1接分喽
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+1f1 差
aa 1
bb 2
cc 4
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
insert into tb values('AA', 1 )
insert into tb values('BB', 3 )
insert into tb values('CC', 7 )
goselect 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 - 1drop table tb/*
F_1 F_2
---------- -----------
AA 1
BB 2
CC 4(所影响的行数为 3 行)
*/
insert into tb values('AA', 1 )
insert into tb values('BB', 3 )
insert into tb values('CC', 7 )
goselect 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 - 1drop table tb/*
F_1 F_2
---------- -----------
AA 1
BB 2
CC 4(所影响的行数为 3 行)
*/
[/code]
create table tb(F_1 varchar(8) , F_2 int)
insert into tb values('AA', 1 )
insert into tb values('BB', 3 )
insert into tb values('CC', 7 )
goselect a.F_1, F_2=a.F_2-isnull(max(b.F_2),0)
from tb a left join tb b on a.F_2>b.F_2
group by a.F_1, a.F_2drop table tb
insert into @T values('AA', 1 )
insert into @T values('BB', 3 )
insert into @T values('CC', 7 )--根据F_1列判断
select
F_1,F_2-isnull((select F_2 from @T where F_1=(select max(F_1) from @T where F_1<t.F_1)),0) as F_2
from
@T t
/*(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)F_1 F_2
---------- -----------
AA 1
BB 2
CC 4(所影响的行数为 3 行)
*/---2005
select
t.F_1,t.F_2-t2.F_2 as F_2
from
(select *, row=row_number()over(order by F_1)from @T)T
left join
(select *, row=row_number()over(order by F_1)from @T)T2 on t.row=T2.row-1
-----------2000没有F_1\F_2没有大小关系时,只有通过生成临时表实现
这一句怎么理解呢?
drop table tb
go
create table tb (F_1 varchar(10),F_2 int)
insert into tb
select 'AA',1 union all
select 'BB',3 union all
select 'CC',7 declare @t table(id int identity(1,1),F_1 varchar(10),F_2 int)
insert into @t select * from tb
select top 1 F_1,F_2 from @t
union all
select a.F_1,b.F_2-a.F_2 as F_2 from @t a,@t b
where a.id=b.id-1接分喽
TABLE A:
F_1 F_2 SUM
0 0 100
30 0 0
0 10 0得到结果:
F_1 F_2 SUM
0 0 100
30 0 130
0 10 120既:下一行的SUM等于上一行的SUM加上下一行的(F_1-F2)