判断连号范围查询例子

表Tb有两个长整型字段F1,F2

F1    F2
---------------------------
100  109
110  119
120  129
140  149
150  159
160  169


问题：
100 至 129 是连续的，140 至 169是连续的，如何得到

F1    F2
---------------------------
100  129
140  169

 

--> 生成测试数据表:tb

If not object_id('[tb]') is null
    Drop table [tb]
Go
Create table [tb]([F1] int,[F2] int)
Insert [tb]
Select 100,109 union all
Select 110,119 union all
Select 120,129 union all
Select 140,149 union all
Select 150,159 union all
Select 160,169
Go
--Select * from [tb]

-->SQL2005查询如下:
select a.F1,b.F2
from
(
    select rn=row_number()over(order by F1),F1 from tb t
    where not exists(select 1 from tb where abs(F2-t.F1)=1)
) a
join
(
    select rn=row_number()over(order by F2),F2 from tb t
    where not exists(select 1 from tb where abs(t.F2-F1)=1)
) b
on a.rn=b.rn
/*
F1          F2
----------- -----------
100         129
140         169

(2 行受影响)
*/

 

-->SQL2000查询如下:
select a.F1,min(b.F2) F2
from
(
    select F1 from tb t
    where not exists(select 1 from tb where abs(F2-t.F1)=1)
) a
join
(
    select F2 from tb t
    where not exists(select 1 from tb where abs(t.F2-F1)=1)
) b
on a.F1<=b.F2
group by a.F1
/*
F1          F2
----------- -----------
100         129
140         169

(2 行受影响)
*/
http://topic.youkuaiyun.com/u/20090903/21/2aa383d6-ee3f-4236-9f4b-994d8deb7699.html?77772