DECLARE @tb TABLE([a] int) INSERT INTO @tb SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 18SELECT CASE WHEN T3.A = MIN(T4.A) THEN CAST(T3.A AS VARCHAR) ELSE CAST(T3.A AS VARCHAR) + '-' + CAST(MIN(T4.A) AS VARCHAR) END FROM (SELECT * FROM @tb T1 WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE A = T1.A - 1))T3, (SELECT * FROM @tb T2 WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE A = T2.A + 1))T4 WHERE T3.A <= T4.A GROUP BY T3.A --结果 1-4 9-12 18 --或者: DECLARE @RE VARCHAR(1000) SET @RE = '' SELECT @RE = @RE + CASE WHEN T3.A = MIN(T4.A) THEN CAST(T3.A AS VARCHAR) ELSE CAST(T3.A AS VARCHAR) + '-' + CAST(MIN(T4.A) AS VARCHAR) END + ',' FROM (SELECT * FROM @tb T1 WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE A = T1.A - 1))T3, (SELECT * FROM @tb T2 WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE A = T2.A + 1))T4 WHERE T3.A <= T4.A GROUP BY T3.A SELECT LEFT(@RE, LEN(@RE) - 1) --结果 1-4,9-12,18 看楼主要什么结果了,可以选择不同的查询。
用个临时变量效率会比ls高一点 DECLARE @tb TABLE([a] int) INSERT INTO @tb SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 18declare @str varchar(8000) declare @i int set @str='' select @str=@str+(case when @str='' then convert(varchar,a) when @i=a-1 then '' else '-'+convert(varchar,@i)+','+convert(varchar,a) end),@i=a from @tb print @str
如果原表没有按顺序排列,请在我写的语句后面加order by a
只是写了一下在数字连续情况下的代码,还是使用游标实现的,希望能帮上忙: create table [t_test]( [a] int null ) insert into [t_test] select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 18 declare @i int,@start_val int,@temp int,@n int declare t_cur cursor dynamic for select a from [t_test] order by a open t_cur fetch next from t_cur into @i --取得第一条记录值 if @@fetch_status=0 begin set @start_val=@i set @temp=@i while @@fetch_status=0 begin fetch next from t_cur into @i if @i=@temp+1 begin set @temp=@i set @n=@n+1 --当不连续的时候就从这条开始再取记录的值 end else break
end end select convert(varchar(10),@start_val)+'-'+convert(varchar(10),@temp) close t_cur deallocate t_cur drop table [t_test]
INSERT INTO @tb
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 18SELECT CASE WHEN T3.A = MIN(T4.A) THEN CAST(T3.A AS VARCHAR)
ELSE CAST(T3.A AS VARCHAR) + '-' + CAST(MIN(T4.A) AS VARCHAR) END FROM
(SELECT * FROM @tb T1 WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE A = T1.A - 1))T3,
(SELECT * FROM @tb T2 WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE A = T2.A + 1))T4
WHERE T3.A <= T4.A
GROUP BY T3.A
--结果
1-4
9-12
18
--或者:
DECLARE @RE VARCHAR(1000)
SET @RE = ''
SELECT @RE = @RE + CASE WHEN T3.A = MIN(T4.A) THEN CAST(T3.A AS VARCHAR)
ELSE CAST(T3.A AS VARCHAR) + '-' + CAST(MIN(T4.A) AS VARCHAR) END + ',' FROM
(SELECT * FROM @tb T1 WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE A = T1.A - 1))T3,
(SELECT * FROM @tb T2 WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE A = T2.A + 1))T4
WHERE T3.A <= T4.A
GROUP BY T3.A
SELECT LEFT(@RE, LEN(@RE) - 1)
--结果
1-4,9-12,18
看楼主要什么结果了,可以选择不同的查询。
DECLARE @tb TABLE([a] int)
INSERT INTO @tb
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 18declare @str varchar(8000)
declare @i int
set @str=''
select @str=@str+(case when @str='' then convert(varchar,a) when @i=a-1 then ''
else '-'+convert(varchar,@i)+','+convert(varchar,a) end),@i=a from @tb
print @str
create table [t_test](
[a] int null
)
insert into [t_test] select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
union all select 18
declare @i int,@start_val int,@temp int,@n int
declare t_cur cursor dynamic
for select a from [t_test] order by a
open t_cur
fetch next from t_cur into @i --取得第一条记录值
if @@fetch_status=0
begin
set @start_val=@i
set @temp=@i
while @@fetch_status=0
begin
fetch next from t_cur into @i
if @i=@temp+1
begin
set @temp=@i
set @n=@n+1 --当不连续的时候就从这条开始再取记录的值
end
else break
end
end
select convert(varchar(10),@start_val)+'-'+convert(varchar(10),@temp)
close t_cur
deallocate t_cur
drop table [t_test]