这个怎么用查询简单实现?

[gahade]

id num
------------------------------
1 10
2 11
3 22
4 23
5 41
6 44

找出id为1,3,5这3条,条件为11-10=1,23-22=1,44-41=3,范围在3以内的!
谢谢

[WangZWang]

select * from tb as a
where exists(select * from tb where abs(num-a.num) between 1 and 3)

[gahade]

To WangZWang(先来):
不是正确的结果啊

create table #t(id int identity(1,1),num int)
insert into #t(num)
select 10
union all select 11
union all select 22
union all select 23
union all select 41
union all select 44

select * from #t as a
where exists(select * from #t where abs(num-a.num) between 1 and 3)

drop table #t

[gahade]

知道了,加上a.id < #t.id

[WangZWang]

declare @t table (id int identity(1,1),num int)
insert into @t(num)
select 10
union all select 11
union all select 22
union all select 23
union all select 41
union all select 44

select * from @t

select * from @t as a
where exists(select * from @t where (num-a.num) between 1 and 3)

[dba_sunny]

declare #t table (id int identity(1,1),num int)
insert into #t(num)
select 10
union all select 11
union all select 22
union all select 23
union all select 41
union all select 44

select * into #t2 from #t

select #t.*
from #t,#t2
where #t.id=#t2.id and ((select num from #t2 where #t.id=#t2.id+1) -#t.num)<3

[dba_sunny]

create table #t(id int identity(1,1),num int)
insert into #t(num)
select 10
union all select 11
union all select 22
union all select 23
union all select 41
union all select 44

select * into #t2 from #t

select #t.*
from #t,#t2
where #t.id=#t2.id and ((select num from #t2 where #t.id=#t2.id+1) -#t.num)<3

[dba_sunny]

create table #t(id int identity(1,1),num int)
insert into #t(num)
select 10
union all select 11
union all select 22
union all select 23
union all select 41
union all select 44

select * from #t as a
where exists(select * from #t where (num-a.num) between 1 and 3)

[wisdomone]

u

[$扫地僧$]

create table #t(id int identity(1,1),num int)
insert into #t(num)
select 10
union all select 11
union all select 22
union all select 23
union all select 41
union all select 44


select T1.id
from
(select * from #t where id%2>0) T1,
(select * from #t where id%2=0) T2
where T1.id=T2.id-1
and T2.num-T1.num <=3