--给你一个Sample,参考下
create table tb(编号 varchar(10))
insert tb select 1
union all select 2
union all select 5
union all select 6
union all select 7
union all select 8
union all select 10
union all select 12
go--查询处理
select 编号=b.编号+'-'+b.编号1
from tb a,(
select 编号,编号1=(select min(编号) from tb a1
where 编号>=a.编号
and not exists(
select * from tb
where 编号=a1.编号+1))
from tb a
where not exists(
select * from tb
where 编号=a.编号-1))b
where a.编号 between b.编号 and b.编号1
group by b.编号,b.编号1
go--删除测试
drop table tb
--结果
编号
---------------------
1-10
10-10
12-12
5-8(所影响的行数为 4 行)
create table tb(编号 varchar(10))
insert tb select 1
union all select 2
union all select 5
union all select 6
union all select 7
union all select 8
union all select 10
union all select 12
go--查询处理
select 编号=b.编号+'-'+b.编号1
from tb a,(
select 编号,编号1=(select min(编号) from tb a1
where 编号>=a.编号
and not exists(
select * from tb
where 编号=a1.编号+1))
from tb a
where not exists(
select * from tb
where 编号=a.编号-1))b
where a.编号 between b.编号 and b.编号1
group by b.编号,b.编号1
go--删除测试
drop table tb
--结果
编号
---------------------
1-10
10-10
12-12
5-8(所影响的行数为 4 行)
insert #t
select 1 union all
select 2 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 10 union all
select 12select numfrom=snumber,
numto=(
select min(snumber) from #t b
where not exists(select 1 from #t where snumber=b.snumber+1)
and b.snumber>=a.snumber)
from #t a
where not exists(select 1 from #t where snumber=a.snumber-1)
drop table #t
select 1 union all
select 2 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 10 union all
select 12 select id=identity(int),snumber into #1 from t a
where not exists
(select * from t where snumber=a.snumber-1)
select id=identity(int),snumber into #2 from t a
where not exists
(select * from t where snumber=a.snumber+1)
select col=case when a.snumber<>b.snumber then cast(a.snumber as varchar)+'-'+cast(b.snumber as varchar)
else cast(a.snumber as varchar) end
into #3 from #1 a,#2 b
where a.id=b.iddeclare @col1 varchar(100)
update #3 set
@col1=case when @col1 is null then col else @col1+','+col end,
col=@col1select max(col) as col from #3drop table #1,#2,#3
drop table t
col
-------------------------------------------------------------
1-2,5-8,10,12(所影响的行数为 1 行)