连续性问题,被难倒了!寻高手

wooden954 2011-10-18 01:32:36
一系列的数字,各行取值有可能与上一行是连续的,也可能是不连续的,要求对这些取值范围进行统计

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)
...全文
175 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
wooden954 2011-10-25
  • 打赏
  • 举报
回复
http://topic.csdn.net/u/20111018/13/5e338b7e-aeac-4572-bdd0-fe213f85f041.html
相同的帖子,供大家参考
jim138 2011-10-18
  • 打赏
  • 举报
回复
mark
huangdaxiang0726 2011-10-18
  • 打赏
  • 举报
回复
12方法可行
唐诗三百首 2011-10-18
  • 打赏
  • 举报
回复
SQL2000方法,

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 identity(int,1,1) rn, a.val into #s
from Test a
left join Test b on a.val-1=b.val
where b.val is null

select identity(int,1,1) rn, a.val into #e
from Test a
left join Test b on a.val=b.val-1
where b.val is null

select #s.val '开始',#e.val '结束'
from #s
inner join #e on #s.rn=#e.rn

开始 结束
----------- -----------
1 4
7 9
15 16
--小F-- 2011-10-18
  • 打赏
  • 举报
回复
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 行受影响)
*/
wooden954 2011-10-18
  • 打赏
  • 举报
回复
可能是我没有说清楚条件,以致答案超出点预期(但会灼情给分)
条件:
仅SQL Server 2000语句有效,其它版本的数据库暂不考虑
水族杰纶 2011-10-18
  • 打赏
  • 举报
回复
典型的孤岛问题
水族杰纶 2011-10-18
  • 打赏
  • 举报
回复
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 2011-10-18
  • 打赏
  • 举报
回复
感谢楼上各位,大家回答的好快呀
fa_ge 2011-10-18
  • 打赏
  • 举报
回复


Create Table # (id int identity(1,1),Val int)

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


select ltrim(min(val))+'-'+ltrim(max(val))
from
(
Select *,val-id v from # a
)a
group by v



/*

-------------------------
1-4
7-9
15-16

(3 row(s) affected)


*/


快溜 2011-10-18
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 fredrickhu 的回复:]
SQL code
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……
[/Quote].
-晴天 2011-10-18
  • 打赏
  • 举报
回复
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
NBDBA 2011-10-18
  • 打赏
  • 举报
回复
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

;with cte as (
select *,ROW_NUMBER () over (order by num) as id from @t a
where not exists (
select 1 from @t
where num = a.num - 1
)
), cte1 as (
select *,ROW_NUMBER () over (order by num) as id from @t a
where not exists (
select 1 from @t
where num = a.num + 1
)
)
select cte.num ,cte1.num from cte ,cte1
where cte.id = cte1.id

--结果
num num
1 5
12 12
17 20
25 25
--小F-- 2011-10-18
  • 打赏
  • 举报
回复
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 行受影响)*/
wooden954 2011-10-18
  • 打赏
  • 举报
回复
自己坐在沙发上等待

22,301

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧