(难)找出自动增长列丢失的ID部分
数据：
ID
1
2
3
6
10
11
20

查询结果:
4 5
7 9
12 19
.. ..
[最优解释]

;with t
as
(
select 1 as ID union all select 
2 union all select 
3 union all select 
6 union all select 
10 union all select 
11 union all select 
20)
,t2
as(
select *,ID-ROW_NUMBER()over(order by ID) as grp
from t
),t3
as
(select ID,DENSE_RANK()over(order by grp) as grp2 from t2 )
select max(a.ID)+1 as ID,min(b.ID)-1 as ID2
from t3 as a 
inner join t3 as b on a.grp2=b.grp2-1
group by a.grp2

/*
IDID2
45
79
1219
*/

[其他解释]
e.g.

use tempdb
go
if object_id('#') is not null drop table #;
create table #(ID int)
insert into #(ID)values
(1),(2),(3),(6),(10),(11),(20)
go
select a.ID +1 As [Begin] ,b.ID-1 As [End] 
From # a
inner join # b on b.ID>a.ID
And b.id=(select min(x.ID) from # x where x.ID>a.ID)
where not exists(select 1 from # x where x.id=a.ID+1)
/*
BeginEnd
-------------------
45
79
1219
*/

[其他解释]
搞一个自增的id，然后和这个对比就可以拉
[其他解释]
本帖最后由 roy_88 于 2012-12-06 21:58:17 编辑

;with t
as
(
select 1 as ID union all select 
2 union all select 
3 union all select 
6 union all select 
10 union all select 
11 union all select 
20)

select a.ID+1 as ID,MIN(b.ID)-1 as ID2
from (select *from t as a where not exists(select 1 from t where ID=a.ID+1)) as a
,(select *from t as a where not exists(select 1 from t where ID=a.ID-1)) as b
where a.ID<b.ID
group by a.ID
/*
IDID2
45
79
1219
*/

[其他解释]

看到了O(∩_∩)O~