如何取出表中连续的自然数

[freetd]

如题，有个表A：
COL
1
2
3
4
5
7
8
9
11
13
20
21
22
23
24
25
26
27
........
取出达到5个及超过5个连续的所有自然数
RESULT：
1
2
3
4
5
20
21
22
23
24
25
26
27

[华夏小卒]

--> 测试数据: [ty]
if object_id('[ty]') is not null drop table [ty]
go
create table [ty] (COL int)
insert into [ty]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 7 union all
select 8 union all
select 9 union all
select 11 union all
select 13 union all
select 20 union all
select 21 union all
select 22 union all
select 23 union all
select 24 union all
select 25 union all
select 26 union all
select 27

alter table ty add id int identity(1,1) 

select col from ty where col-id in ( 
select col-id col1 from [ty] group by col-id having(count(*)>=5)
)

col
-----------
1
2
3
4
5
20
21
22
23
24
25
26
27

(13 行受影响)

[永生天地]

连续数据（数字，日期）的个数
http://blog.csdn.net/xys_777/archive/2010/06/21/5683641.aspx

[freetd]

不用自增列能不能直接查出来

[黄_瓜]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int)
insert [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 7 union all
select 8 union all
select 9 union all
select 11 union all
select 13 union all
select 20 union all
select 21 union all
select 22 union all
select 23 union all
select 24 union all
select 25 union all
select 26 union all
select 27

--------------------------------查询开始------------------------------
;with t as
(
select id,row=row_number() over(order by id) from tb
)
select id from t where id-row in 
(
	select id-row from  t group by id-row having count(*)>=5
)

/*
id
-----------
1
2
3
4
5
20
21
22
23
24
25
26
27

(13 行受影响)


*/

[ChinaJiaBing]

---try

declare @tb table (col int)
insert into @tb select 1
     union all  select 2
     union all  select 3
     union all  select 4
     union all  select 5
     union all  select 7
     union all  select 8
     union all  select 9
     union all  select 11
     union all  select 13
     union all  select 20
     union all  select 21
     union all  select 22
     union all  select 23
     union all  select 24
     union all  select 25
     union all  select 26
     union all  select 27
select col from 
           (select id1=col-id from (select col,id=(select count(*) from @tb where col<=a.col) from @tb a) tb
           group by col-id having(count(*)>=5)) a ,
           (select *, id1=col-id from (select col,id=(select count(*) from @tb where col<=a.col) from @tb a) tb)
b  where a.id1=b.id1



/*

(18 行受影响)
col
-----------
1
2
3
4
5
20
21
22
23
24
25
26
27

(13 行受影响)



*/

[SQL77]

--> 测试数据: [ty]
if object_id('[ty]') is not null drop table [ty]
go
create table [ty] (COL int)
insert into [ty]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 7 union all
select 8 union all
select 9 union all
select 11 union all
select 13 union all
select 20 union all
select 21 union all
select 22 union all
select 23 union all
select 24 union all
select 25 union all
select 26 union all
select 27


SELECT *,0 AS IDD  INTO #T FROM TY
 
DECLARE @COL INT,@NUM INT 
SET @NUM=0

UPDATE #T SET IDD=@NUM,@NUM=CASE WHEN @COL=COL-1 THEN @NUM ELSE ISNULL(@NUM,0)+1 END ,@COL=COL

SELECT * FROM #T WHERE IDD IN(SELECT IDD FROM #T GROUP BY IDD HAVING COUNT(1)>=5)

/*COL         IDD         
----------- ----------- 
1           1
2           1
3           1
4           1
5           1
20          5
21          5
22          5
23          5
24          5
25          5
26          5
27          5

（所影响的行数为 13 行）

[abuying]

学习了。

[abuying]

--SQL 2005 使用row_numer()排序函数来解决
select e.col from 
(select id=a.col-a,count(*) as icount from 
   (select *,Rank() over(order by col) as a from tb) as a
     inner join tb as b on a.col=b.col group by a.col-a having(count(*))>=5) as d
inner join 
(select a.col,id=a.col-a  from 
     (select *,Rank() over(order by col) as a from tb) as a
         inner join tb as b on a.col=b.col  ) as e 
on d.id=e.id