34,838
社区成员




declare @T table (data int)
insert into @T
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 6 union all
select 7 union all
select 9 union all
select 11 union all
select 12 union all
select 13
select min(data),max(data) from
(
select *,rid=(select count(1)+1 from @t where data<a.data) from @t a
) b group by data-rid
/*
1 4
6 7
9 9
11 13
*/
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([data] int)
Insert #1
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 6 union all
select 7 union all
select 9 union all
select 11 union all
select 12 union all
select 13
Go
SELECT
RTRIM(MIN(data))+'-'+RTRIM(MAX(Data)) AS Data
FROM (Select *,ROW_NUMBER()OVER(ORDER BY data)-[data] AS rn from #1)t
GROUP BY rn
ORDER BY MIN(data)
/*
1-4
6-7
9-9
11-13
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([data] int)
insert [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 6 union all
select 7 union all
select 9 union all
select 11 union all
select 12 union all
select 13
select a.data,b.data
from
(
select rn=row_number() over(order by getdate()),*
from [tb] t where not exists(select 1 from tb where data=t.data-1)
) a
join
(
select rn=row_number() over(order by getdate()),*
from [tb] t where not exists(select 1 from tb where data=t.data+1)
) b
on a.rn=b.rn
--测试结果:
/*
data data
----------- -----------
1 4
6 7
9 9
11 13
(4 行受影响)
*/
declare @T table (data int)
insert into @T
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 6 union all
select 7 union all
select 9 union all
select 11 union all
select 12 union all
select 14 union all
select 15 union all
select 111 union all
select 112 union all
select 113 union all
select 114
select cast(MIN(data)as varchar(50))+'-'+cast(MAX(data) as varchar(50)) from( select data ,rn=ROW_NUMBER()over( order by data)-data from @T)s
group by rn
order by MIN(data)
(15 行受影响)
-----------------------------------------------------------------------------------------------------
1-4
6-7
9-9
11-12
14-15
111-114
(6 行受影响)