(难)找出自动增长列丢失的ID部分,该如何处理
日期：2014-05-17 浏览次数：20491 次
(难)找出自动增长列丢失的ID部分
数据：
ID
1
2
3
6
10
11
20
查询结果:
4 5
7 9
12 19
.. ..
------解决方案--------------------
------解决方案--------------------
e.g.
数据：
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
*/
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
