表结构如下:
create table dbo.T1
(
id int primary key
);
insert into dbo.T1
(
id
)
values (2),(3),(11),(12),(13),(27),(33),(34),(35),(42);
select *
from dbo.T1 with(nolock)
order by id;
观察数据可以发现,这批数据中存在几个连续:2-3,11-13,33-35,还有两个独立的数:27-27,42-42。有时候我们需要找出现有值的范围,又称孤岛,该怎么入手呢?方法很多,这里介绍一个高效的方法:
;with aaa as
(
select ROW_NUMBER() over(order by id) as rowindex,
id
from dbo.T1 with(nolock)
)
,bbb as
(
select *,
id-rowindex as diffindex
from aaa
)
select MIN(id) as StartId,
MAX(id) as EndId
from bbb
group by diffindex
order by diffindex;
怎么样,轻松吧,两个CTE搞定。这是最简单的应用,下次再介绍一个稍微复杂点的应用。