• 主页
• 基础类
• 应用实例
• 新技术前沿

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

wooden954 2011-10-18 01:50:07

1,2,3,4,7,8,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)
``````

...全文
89 点赞 收藏 10

10 条回复
wooden954 2011年10月25日

mark

--小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日

``````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日

-晴天 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``````

--小F-- 2011年10月18日
num不连续的话加个自增列。

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

MS-SQL Server

1.4w+

25.3w+

MS-SQL Server相关内容讨论专区