这个格式怎么变的来着？不记得了。

[geniuswjt]

data:
1
2
3
4
6
7
9
11
12
13
……
result:
1-4
6-7
9-9
11-13
……

也就是判断连续的。记得之前看过水哥还是谁的操作来着，有知情者贴下么。

[勿勿]

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 行受影响)

[geniuswjt]

嗯，取差值group by的比较讨巧。
有种意识的体现的感觉，不错，结贴。

[小和桑]

坐等知情者

[百年树人]

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 行受影响)
*/

[中国风]

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
*/

[水族杰纶]

技术内幕-T-SQL查询之孤岛和间断
256页

[叶子]

数据不大的话，辅助master..spt_values应该也可以吧...

[叶子]

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
*/

SQL SERVER 2000没有row_number的话，这样就可以了，不需要用master..spt_values。