求助sql实现序列号的连续合并..
一个表
字段 :snumber(int)
1
2
5
6
7
8
10
12
用sql转化成
1-2,5-8,10,12
先谢谢各位大虾!!
问题点数：100、回复次数：5Top
1 楼zlp321002（Life Is Good,Let's Shine）回复于 2005-11-29 22:10:51 得分 30
--给你一个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 行）
Top
2 楼pbsql（风云）回复于 2005-11-29 22:23:54 得分 20
create table #t(snumber int)
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 12
select 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 #tTop
3 楼pbsql（风云）回复于 2005-11-29 22:24:31 得分 0
返回：
1 2
5 8
10 10
12 12
Top
4 楼samfeng_2003（凤翼天翔）回复于 2005-11-29 22:27:22 得分 50
create table t (snumber int)
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 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.id
declare @col1 varchar(100)
update #3 set
@col1=case when @col1 is null then col else @col1+','+col end,
col=@col1
select max(col) as col from #3
drop table #1,#2,#3
drop table t
col
-------------------------------------------------------------
1-2,5-8,10,12
（所影响的行数为 1 行）
Top
5 楼yosoft（）回复于 2005-12-09 15:25:09 得分 0
不好意思，一直在忙，谢谢各位拉！！Top
