--> Title : Generating test data [tb] --> Author : --> Date : 2009-12-01 17:07:09 if object_id('[tb]') is not null drop table [tb] go create table [tb] (ID int) insert into [tb] select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 12 union all select 17 union all select 18 union all select 19 union all select 20 union all select 25 select case when min(t.id)<>max(t.id) then cast(min(t.id) as varchar)+'-'+cast(max(t.id) as varchar) else cast(min(t.id) as varchar) end from ( select id,id-(select count(*) from tb b where a.id>=b.id) groupid from tb a ) t group by groupid /* ------------------------------------------------------------- 1-5 12 17-20 25(4 個資料列受到影響) */
declare @t table(num int) insert into @t select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 12 union all select 17 union all select 18 union all select 19 union all select 20 union all select 25 select rtrim(a.num)+(case when min(b.num)!=a.num then '-'+rtrim(min(b.num)) else '' end) from (select t.num from @t t where not exists(select 1 from @t where num=t.num-1)) a, (select t.num from @t t where not exists(select 1 from @t where num=t.num+1)) b where a.num<=b.num group by a.num/* ------------------------- 1-5 12 17-20 25 */
--------------------------------------------- --> Author : js_szy --> Target : 各位大大,小卒就是想要一朵花 --> Date : 2009-12-01 17:08:51 --> Version: SQL Server 2005 ---------------------------------------------
--> 测试数据: @tb declare @tb table (id int) insert into @tb select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 12 union all select 17 union all select 18 union all select 19 union all select 20 union all select 25declare @s varchar(100);with szy as ( select id,px=id-row_number()over(order by id) from @tb )
select @s=isnull(@s+',','')+ltrim(id) from ( select px,id=ltrim(min(id))+case when min(id)=max(id) then '' else '-'+ltrim(max(id)) end from szy group by px )y select @s---------------------------------------------------------------------------------------------------- 1-5,12,17-20,25(1 行受影响)
--> Author :
--> Date : 2009-12-01 17:07:09
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (ID int)
insert into [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 12 union all
select 17 union all
select 18 union all
select 19 union all
select 20 union all
select 25
select case when min(t.id)<>max(t.id) then cast(min(t.id) as varchar)+'-'+cast(max(t.id) as varchar) else cast(min(t.id) as varchar) end
from
(
select id,id-(select count(*) from tb b where a.id>=b.id) groupid
from tb a
) t
group by groupid
/*
-------------------------------------------------------------
1-5
12
17-20
25(4 個資料列受到影響)
*/
insert into @t select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 12
union all select 17
union all select 18
union all select 19
union all select 20
union all select 25 select
rtrim(a.num)+(case when min(b.num)!=a.num then '-'+rtrim(min(b.num)) else '' end)
from
(select t.num from @t t where not exists(select 1 from @t where num=t.num-1)) a,
(select t.num from @t t where not exists(select 1 from @t where num=t.num+1)) b
where
a.num<=b.num
group by
a.num/*
-------------------------
1-5
12
17-20
25
*/
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date : 2009-12-01 17:08:51
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
declare @tb table (id int)
insert into @tb
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 12 union all
select 17 union all
select 18 union all
select 19 union all
select 20 union all
select 25declare @s varchar(100);with szy as
(
select id,px=id-row_number()over(order by id) from @tb
)
select @s=isnull(@s+',','')+ltrim(id) from
( select px,id=ltrim(min(id))+case when min(id)=max(id) then '' else '-'+ltrim(max(id)) end
from szy
group by px
)y
select @s----------------------------------------------------------------------------------------------------
1-5,12,17-20,25(1 行受影响)