连续性问题，被难倒了！寻高手

[wooden954]

一系列的数字，各行取值有可能与上一行是连续的，也可能是不连续的，要求对这些取值范围进行统计
如
1,2,3,4,7,8,9,15,16
统计结果应是1-4,7-9,15-16
单独的统计我知道怎么做，但是要求结果把统计区间的开始值与终止值存到一行中去，即结果如下
开始 结束
1 4
7 9
15 16

请使用以下语句测试

Create Table Test (Val int)

insert into test (val) values (1)  --1-4
insert into test (val) values (2)
insert into test (val) values (3)
insert into test (val) values (4)  
insert into test (val) values (7)  --7-9
insert into test (val) values (8)
insert into test (val) values (9)
insert into test (val) values (15)  --15-16
insert into test (val) values (16)

Select * from test

我已经实现的分别统计区间的起始值和终止值，不知道这些语句对最终结果是否有帮助，可以参考一下

--查出连续数据的最小值
select val from test a 
where not exists 
    (select top 1 val from test b where b.val = a.val-1)
--查出连续数据的最大值
select val from test a 
where not exists 
    (select top 1 val from test b where b.val = a.val+1)

[又见朝阳]

没弄懂意思, 不晓得出的啥题/

[--小F--]

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) as col,(case when min(b.num)!=a.num then rtrim(min(b.num)) else '' end) as col2 
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           

(4 行受影响)*/

[--小F--]

num不连续的话加个自增列。

[-晴天]

Create Table Test (Val int)

insert into test (val) values (1)  --1-4
insert into test (val) values (2)
insert into test (val) values (3)
insert into test (val) values (4)  
insert into test (val) values (7)  --7-9
insert into test (val) values (8)
insert into test (val) values (9)
insert into test (val) values (15)  --15-16
insert into test (val) values (16)

go
;with cte as(
select val,val as val1 from test a where not exists(select 1 from test where val=a.val-1)
union all
select a.val,b.val from cte a inner join test b on a.val1=b.val-1
)select val,max(val1)val1 from cte group by val
/*
val         val1
----------- -----------
1           4
7           9
15          16

(3 行受影响)

*/
go
drop table test

[wooden954]

感谢楼上各位，大家回答的好快呀

[水族杰纶]

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 min(num)start,
       max(num)[end]
from
(select num ,num-row_number()over(order by num)as grp
 from @t)as D
group by grp
/*
start       end
----------- -----------
1           5
12          12
17          20
25          25

*/

[wooden954]

可能是我没有说清楚条件，以致答案超出点预期（但会灼情给分）
条件：
仅SQL Server 2000语句有效，其它版本的数据库暂不考虑

[--小F--]

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 IDENTITY(int,1,1) as id,* into #t from @t

select
 min(num)start,
 max(num)[end]
from
 (select num ,num-id as grp from #t)as D group by grp

     
     
drop table #t

      
/*start       end
----------- -----------
1           5
12          12
17          20
25          25

(4 行受影响)
*/

[稻草_木偶]

mark

[wooden954]

http://topic.csdn.net/u/20111018/13/d224029e-c6d3-470e-9d94-2c4addbca272.html
相同的帖子，供大家参考

结帖了，给分