create table #t1(id int identity(1,1),num int) insert into #t1(num) select 1 union select 2 union select 300 union select 90000 union select 450 union select 200select * from #t1 a where abs(num-(select top 1 num from #t1 b where b.id<a.id))>60000drop table #t1 -------------------- id num 6 90000 ------------------(1 行受影响)
create table #t1(id int,num int) insert into #t1 select 1,1 union select 2,2 union select 3,300 union select 4,90000 union select 5,90001 union select 6,90003 union select 7,900000 select * from #t1 select * from #t1 a where abs(num-(select top 1 num from #t1 b where b.id<a.id order by id desc))>60000drop table #t1 -------------------------- 4 90000 7 900000 ------------------------- (2 行受影响)
---是否这样? ---创建测试环境 Declare @T Table(id int,value int) Insert @T Select 1,1 Union ALl Select 2,60001 Union ALl Select 3,70001 Union ALl Select 4,80001 Union ALl Select 5,140001 Union ALl Select 6,200001 Select * From @T ---查询结果 Select * From @T Where id In( Select A.id From @T As A,@T As B Where A.id+1=B.id And B.Value-A.Value>=60000 Union Select A.id+1 As id From @T As A,@T As B Where A.id+1=B.id And B.Value-A.Value>=60000 ) Order By id ---结果 /* id value ----------- ----------- 1 1 2 60001 4 80001 5 140001 6 200001(所影响的行数为 5 行) */
create table # ( id int identity(1,1), qty int )insert into # select 2 union all select 60002 union all select 0 union all select 0 union all select 0 union all select 200 union all select 70000 union all select 100 select a.* from # a where abs(a.qty -(select top 1 qty from # where id>a.id order by id ))>=60000 and a.id<(select top 1 id from # where id>a.id order by id ) id qty ----------- ----------- 1 2 2 60002 6 200 7 70000(4 row(s) affected)
--将数据插入一临时表,生成一连号的ID select id = identity(int,1,1) , * into tmp from tbselect a.* from tmp a , tmp b where abs(a.col - b.col) > 60000 and a.id = b.id + 1
insert into #t1(num) select 1
union select 2
union select 300
union select 90000
union select 450
union select 200select * from #t1 a where abs(num-(select top 1 num from #t1 b where b.id<a.id))>60000drop table #t1
--------------------
id num
6 90000
------------------(1 行受影响)
insert into #t1 select 1,1
union select 2,2
union select 3,300
union select 4,90000
union select 5,90001
union select 6,90003
union select 7,900000
select * from #t1
select * from #t1 a where abs(num-(select top 1 num from #t1 b where b.id<a.id order by id desc))>60000drop table #t1
--------------------------
4 90000
7 900000
-------------------------
(2 行受影响)
---创建测试环境
Declare @T Table(id int,value int)
Insert @T Select 1,1
Union ALl Select 2,60001
Union ALl Select 3,70001
Union ALl Select 4,80001
Union ALl Select 5,140001
Union ALl Select 6,200001
Select * From @T
---查询结果
Select * From @T Where id In(
Select
A.id
From
@T As A,@T As B
Where A.id+1=B.id And B.Value-A.Value>=60000
Union
Select
A.id+1 As id
From
@T As A,@T As B
Where A.id+1=B.id And B.Value-A.Value>=60000
) Order By id
---结果
/*
id value
----------- -----------
1 1
2 60001
4 80001
5 140001
6 200001(所影响的行数为 5 行)
*/
(
id int identity(1,1),
qty int
)insert into #
select 2 union all
select 60002 union all
select 0 union all
select 0 union all
select 0 union all
select 200 union all
select 70000 union all
select 100
select a.* from # a
where abs(a.qty -(select top 1 qty from # where id>a.id order by id ))>=60000
and a.id<(select top 1 id from # where id>a.id order by id ) id qty
----------- -----------
1 2
2 60002
6 200
7 70000(4 row(s) affected)
select id = identity(int,1,1) , * into tmp from tbselect a.* from tmp a , tmp b where abs(a.col - b.col) > 60000 and a.id = b.id + 1