--> Test Data: [tb1]
if object_id('[tb1]') is not null drop table [tb1]
create table [tb1] ([Type] int,[Num] int)
insert into [tb1]
select 1,1000 union all
select 1,1001 union all
select 1,1002 union all
select 2,1 union all
select 2,2 union all
select 2,3 union all
select 2,12 union all
select 2,13 union all
select 2,14--select * from [tb1]
--Code
select distinct Type,
StartNum=(select min(num) from tb1 where Type=t.Type and len(num)=len(t.Num)),
EndNum=(select max(num) from tb1 where Type=t.Type and len(num)=len(t.Num))
from tb1 t
--Drop--Result
/*
Type StartNum EndNum
----------- ----------- -----------
1 1000 1002
2 1 3
2 12 14
if object_id('[tb1]') is not null drop table [tb1]
create table [tb1] ([Type] int,[Num] int)
insert into [tb1]
select 1,1000 union all
select 1,1001 union all
select 1,1002 union all
select 2,1 union all
select 2,2 union all
select 2,3 union all
select 2,12 union all
select 2,13 union all
select 2,14--select * from [tb1]
--Code
select distinct Type,
StartNum=(select min(num) from tb1 where Type=t.Type and len(num)=len(t.Num)),
EndNum=(select max(num) from tb1 where Type=t.Type and len(num)=len(t.Num))
from tb1 t
--Drop--Result
/*
Type StartNum EndNum
----------- ----------- -----------
1 1000 1002
2 1 3
2 12 14
2> go
type num
----------- -----------
1 1000
1 1001
1 1002
2 1
2 2
2 3
2 12
2 13
2 14(9 rows affected)
1>
1> select a.type,b.num as StartNum, a.num as EndNum
2> from(
3> select ROW_NUMBER() OVER(ORDER BY type, num) AS rowno ,type,num
4> from table1
5> where Num+1 not in (select num from table1)
6> ) a inner join (
7> select ROW_NUMBER() OVER(ORDER BY type, num) AS rowno ,type,num
8> from table1
9> where Num-1 not in (select num from table1)
10> ) b on a.rowno=b.rowno;
11> go
type StartNum EndNum
----------- ----------- -----------
1 1000 1002
2 1 3
2 12 14(3 rows affected)
1>
from
(
select type,diff,mix(num) as startnum,max(num) as endnum
from
(
select type,num,row_num-num as diff
from
(
select type,num,row_number() over(order by type,num) as row_num
from tb
)T1
)T2
group by type,diff
)T3
而且在几千条数据的情况下效率很差..