(难)找到自动增长列丢失的ID部分
日期：2014-05-17 浏览次数：20462 次
(难)找出自动增长列丢失的ID部分
数据：
ID
1
2
3
6
10
11
20
查询结果:
4 5
7 9
12 19
.. ..
------最佳解决方案--------------------
------其他解决方案--------------------
e.g.
------其他解决方案--------------------
搞一个自增的id，然后和这个对比就可以拉
------其他解决方案--------------------
本帖最后由 roy_88 于 2012-12-06 21:58:17 编辑
------其他解决方案--------------------
看到了O(∩_∩)O~
数据：
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
/*
ID ID2
4 5
7 9
12 19
*/
------其他解决方案--------------------
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)
/*
Begin End
-------------------
4 5
7 9
12 19
*/
------其他解决方案--------------------
搞一个自增的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
/*
ID ID2
4 5
7 9
12 19
*/
------其他解决方案--------------------
看到了O(∩_∩)O~
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
