现在有一张表中有个字段为编号[字符串],现在检索出相关记录.如何对这些编号进行连号处理:
如:
检索出编号为 1,5,7,3,2,4,15,16,8,9,10,13,14 这些记录
如何统计使之成为 : "编号统计为: 1-5,7-10,13-16"
这样的结果?
简化为"有这样一个字符串"1,5,7,3,2,4,15,16,8,9,10,13,14"
如何处理使之成为 1-5,7-10,13-16 这样的字符串? "
要求:1.号码是连续的就取区间用-连接
2.号码不连续的就用逗号隔开并分段显示
如:
检索出编号为 1,5,7,3,2,4,15,16,8,9,10,13,14 这些记录
如何统计使之成为 : "编号统计为: 1-5,7-10,13-16"
这样的结果?
简化为"有这样一个字符串"1,5,7,3,2,4,15,16,8,9,10,13,14"
如何处理使之成为 1-5,7-10,13-16 这样的字符串? "
要求:1.号码是连续的就取区间用-连接
2.号码不连续的就用逗号隔开并分段显示
go
insert a values(1) insert a values(5)
insert a values(7) insert a values(3)
insert a values(2) insert a values(4)
insert a values(15) insert a values(16)
insert a values(8) insert a values(9)
insert a values(10) insert a values(13)
insert a values(14)select * into b from a order by a asc
go
declare @res varchar(500)set @res=''
select @res=cast(a.a as varchar)+'-'+cast(b.a as varchar)+','+@res
from
(select ROW_NUMBER() OVER(ORDER BY a DESC) AS id,a from b tem
where not exists(select 1 from b where a+1=tem.a)) a,
(select ROW_NUMBER() OVER(ORDER BY a DESC) AS id, a from b tem
where not exists(select 1 from b where a-1=tem.a)) b
where a.id=b.idselect @res
insert @t values ('5')
insert @t values ('7')
insert @t values ('3')
insert @t values ('2')
insert @t values ('4')
insert @t values ('15')
insert @t values ('16')
insert @t values ('8')
insert @t values ('9')
insert @t values ('10')
insert @t values ('13')
insert @t values ('14')if object_id('tempdb..#t')is not null
drop table tempdb..#tselect i=identity(int,1,1),a.* into #t from (select top 100 percent * from @t order by cast(id as int))a
select min(id)+'-'+max(id) from
(select id,id-i as c from #t)a group by c---------------------
1-5
10-9
13-16(所影响的行数为 3 行)
insert @t values ('5')
insert @t values ('7')
insert @t values ('3')
insert @t values ('2')
insert @t values ('4')
insert @t values ('15')
insert @t values ('16')
insert @t values ('8')
insert @t values ('9')
insert @t values ('10')
insert @t values ('13')
insert @t values ('14')if object_id('tempdb..#t')is not null
drop table tempdb..#tselect i=identity(int,1,1),a.* into #t from (select top 100 percent * from @t order by cast(id as int))a
select cast(min(cast(id as int)) as varchar(10))+'-'+cast(max(cast(id as int)) as varchar(10)) from
(select id,id-i as c from #t)a group by c---------------------
1-5
7-10
13-16(所影响的行数为 3 行)
修改一点