全部

如何生成号码段

Changefish 2009-02-24 02:06:13

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

...全文

125 点赞收藏 12

写回复

12 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

swwei2002 2009-02-24



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

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

回复赞

肥龙上天 2009-02-24

[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 2009-02-24

学习

回复赞

dobear_0922 2009-02-24

--> 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]

回复赞

dobear_0922 2009-02-24

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

回复赞

dobear_0922 2009-02-24

--> 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]

回复赞

htl258_Tony 2009-02-24

回复赞

oopp1234567890 2009-02-24

循环，游标都可以

回复赞

htl258_Tony 2009-02-24

我想也是要游标

回复赞

ljhcy99 2009-02-24

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

回复赞

百年树人 2009-02-24

---测试数据---

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 行）