如何生成号码段

[Changefish]

有如下数字:
100001
100002
100003
100004
100008
100012
100013
100014
100023
100030
100031
100032
如何将这些数字生成连续号码段表示:
100001-100004,100008,100012-100014,100023,100030-100032
谢谢

[sdhdy]

估计得用游标实现了。

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 100001 union all
select 100002 union all
select 100003 union all
select 100004 union all
select 100008 union all
select 100012 union all
select 100013 union all
select 100014 union all
select 100023 union all
select 100030 union all
select 100031 union all
select 100032
 
---查询---
select col,tid=identity(int,1,1) into #1 from [tb] t where not exists(select * from tb where col=t.col-1)
select col,tid=identity(int,1,1) into #2 from [tb] t where not exists(select * from tb where col=t.col+1)

declare @result varchar(1000)
select 
  @result=isnull(@result+',','')+
  case 
    when a.col=b.col then ltrim(a.col)
    else ltrim(a.col)+'-'+ltrim(b.col)
  end
from #1 a,#2 b
where a.tid=b.tid

select @result

---结果---
                                                                                    
------------------------------------------------------------------------------
100001-100004,100008,100012-100014,100023,100030-100032

（所影响的行数为 1 行）

[ljhcy99]

存储过程，游标，判断以下数字是否连续， 循环处理

[htl258_Tony]

我想也是要游标

[oopp1234567890]

循环，游标都可以

[htl258_Tony]

[Quote=引用 2 楼 josy 的回复:]
SQL code---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 100001 union all
select 100002 union all
select 100003 union all
select 100004 union all
select 100008 union all
select 100012 union all
select 100013 union all
select 100014 union all
select 100023 union all
select 100030 union all
select 100031 union all
se…
[/Quote]顶

[dobear_0922]

--> By dobear_0922(小熊) 2009-02-24 14:18:06
--> 测试数据：[Numbers]
if object_id('[Numbers]') is not null drop table [Numbers]
create table [Numbers]([Num] int)
insert [Numbers]
select 100001 union all
select 100002 union all
select 100003 union all
select 100004 union all
select 100008 union all
select 100012 union all
select 100013 union all
select 100014 union all
select 100023 union all
select 100030 union all
select 100031 union all
select 100032
declare @info varchar(1000)
select @info=isnull(@info+',', '')+ rtrim(M)
			+ case when M<N then '-'+rtrim(N) else '' end
from (
select M, max(Num) as N  
from (select Num, M=(select Max(Num) from [Numbers] b where Num<=a.Num 
				and not exists(select * from [Numbers] where Num=b.Num-1)) 
	  from [Numbers] a) T 
group by M ) TT

select Info=@info
/*
Info
-----------------------------------------------------------
100001-100004,100008,100012-100014,100023,100030-100032

(1 行受影响)

*/

drop table [Numbers]

[dobear_0922]

最好不要用游标，如果是SQL2005，连变量都不需要用，，，

[dobear_0922]

--> By dobear_0922(小熊) 2009-02-24 14:18:06
--> 测试数据：[Numbers]
if object_id('[Numbers]') is not null drop table [Numbers]
create table [Numbers]([Num] int)
insert [Numbers]
select 100001 union all
select 100002 union all
select 100003 union all
select 100004 union all
select 100008 union all
select 100012 union all
select 100013 union all
select 100014 union all
select 100023 union all
select 100030 union all
select 100031 union all
select 100032

--SQL2005
select Info=stuff((
select ','+MN from
(
select MN=rtrim(M) + case when M<Max(Num) then '-'+rtrim(Max(Num)) else '' end   
from (select Num, M=(select Max(Num) from [Numbers] b where Num<=a.Num 
				and not exists(select * from [Numbers] where Num=b.Num-1)) 
	  from [Numbers] a) T 
group by M )TT
for xml path('')), 1,1,'')

/*
Info
-----------------------------------------------------------
100001-100004,100008,100012-100014,100023,100030-100032

(1 行受影响)

*/

drop table [Numbers]

[swwei2002]

学习

[肥龙上天]

[Quote=引用 2 楼 josy 的回复:]
SQL code---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 100001 union all
select 100002 union all
select 100003 union all
select 100004 union all
select 100008 union all
select 100012 union all
select 100013 union all
select 100014 union all
select 100023 union all
select 100030 union all
select 100031 union all
se…
[/Quote]

树人兄弟的这个语句实在是太经典了，暂时没发现比这更精炼的写法，收藏了

[swwei2002]

select ltrim(min(col))+'-'+ltrim(max(col)) as [begin-end]
from 
(select col,col+row_number() over(order by col desc) as ttl from tb) t 
group by t.ttl

和楼上几位思路不太一样,楼主可以参考一下