2.1w+
社区成员
- 全部
如何生成号码段
有如下数字:
100001
100002
100003
100004
100008
100012
100013
100014
100023
100030
100031
100032
如何将这些数字生成连续号码段表示:
100001-100004,100008,100012-100014,100023,100030-100032
谢谢
100001
100002
100003
100004
100008
100012
100013
100014
100023
100030
100031
100032
如何将这些数字生成连续号码段表示:
100001-100004,100008,100012-100014,100023,100030-100032
谢谢
...全文
当前发帖距今超过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]
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
[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]顶
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]顶
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 行）sdhdy 2009-02-24
估计得用游标实现了。
