连续数据（数字，日期）的个数

--sql2000
select max(t.col1) as id, t.col2,COUNT(1) CNT
from (select col1,col2,rownum=(select count(1)
from tb
where col2=a.col2 and col1<=a.col1)
from tb a) t
group by t.col2,t.col1-rownum

/*
id col2 CNT
----------- ----------- -----------
1 1 1
3 9 2
10 9 6
4 2 1
16 9 5
11 1 1

（所影响的行数为 6 行）

*/
--sql2005
with t as
(select t1.col1,t1.col2,ROW_NUMBER()over(partition by col2 order by col1) as rn from tb t1),
t1 as (
select max(t.col1) as id, t.col2,COUNT(1) CNT
from t
group by t.col2,t.col1-rn

)
select * from t1 where cnt>=5

--结果
/*
id col2 CNT
----------- ----------- -----------
10 9 6
16 9 5

（所影响的行数为 2 行）
*/

--2 连续的时间
if object_id('tb') is not null drop table tb
go
create table tb (vcNO varchar(1),dDate datetime);
insert into tb
select 'A',' 2010-01-01' union all
select 'A',' 2010-01-02' union all
select 'A',' 2010-03-04' union all
select 'B',' 2010-03-05' union all
select 'B',' 2010-03-06' union all
select 'B',' 2010-03-07' union all
select 'B',' 2010-03-08' union all
select 'B',' 2010-03-09' union all
select 'B',' 2010-03-10' union all
select 'B',' 2010-03-11' union all
select 'B',' 2010-03-12' union all
select 'B',' 2010-03-13' union all
select 'B',' 2010-03-14' union all
select 'B',' 2010-03-15' union all
select 'B',' 2010-03-16' union all
select 'B',' 2010-03-17' union all
select 'B',' 2010-03-18' union all
select 'B',' 2010-03-19' union all
select 'B',' 2010-03-20' union all
select 'B',' 2010-03-21' union all
select 'B',' 2010-03-22' union all
select 'B',' 2010-03-23' union all
select 'B',' 2010-03-24' union all
select 'B',' 2010-03-25' union all
select 'B',' 2010-03-26' union all
select 'B',' 2010-03-27' union all
select 'B',' 2010-03-28' union all
select 'B',' 2010-03-29' union all
select 'B',' 2010-03-30' union all
select 'B',' 2010-03-31' union all
select 'B',' 2010-04-01' union all
select 'B',' 2010-04-02' union all
select 'B',' 2010-04-03' union all
select 'B',' 2010-04-04' union all
select 'B',' 2010-04-05' union all
select 'B',' 2010-04-06' union all
select 'B',' 2010-04-07' union all
select 'B',' 2010-04-08' union all
select 'B',' 2010-04-09' union all
select 'B',' 2010-04-10' union all
select 'B',' 2010-04-11'
go
--sql2000
select max(t.vcNO) as vcNO, min(t.dDate) minDate,max(t.dDate) maxDate,COUNT(1) CNT
from (select vcNO,dDate,rownum=(select count(1)
from tb
where vcNO=a.vcNO and dDate<a.dDate)
from tb a) t
group by t.vcNO, dateadd(day,-rownum,dDate)
/*
vcNO minDate maxDate CNT
---- ------------------------------------------------------ ------------------------------------------------------ -----------
A 2010-01-01 00:00:00.000 2010-01-02 00:00:00.000 2
A 2010-03-04 00:00:00.000 2010-03-04 00:00:00.000 1
B 2010-03-05 00:00:00.000 2010-04-11 00:00:00.000 38

（所影响的行数为 3 行）

*/

例子贴：

http://topic.csdn.net/u/20100719/16/807521f9-b6af-4e0c-b172-c5ef9369312d.html?11412

--一个特殊的需求

http://topic.csdn.net/u/20100621/12/066bd0e9-aebe-47c5-841b-0ba248da137d.html?seed=1779032023&r=66379567#r_66379567

select vcNo ,
dateadd(day,((count(1)-1)/30 )*30,min(t.dDate)) minDate,
(case when dateadd(day,((count(1)-1)/30 )*30+29,min(t.dDate))>max(t.dDate) then max(t.dDate)
else dateadd(day,((count(1)-1)/30 )*30+29,min(t.dDate)) end )+1 maxDate
from (select vcNO,dDate,rownum=(select count(1)
from tb
where vcNO=a.vcNO and dDate<a.dDate)
from tb a) t
group by t.vcNO, dateadd(day,-rownum,dDate),(rownum)/30

/*
vcNo minDate maxDate
---- ------------------------------------------------------ ------------------------------------------------------
A 2010-01-01 00:00:00.000 2010-01-03 00:00:00.000
A 2010-03-04 00:00:00.000 2010-03-05 00:00:00.000
B 2010-03-05 00:00:00.000 2010-04-04 00:00:00.000
B 2010-04-04 00:00:00.000 2010-04-12 00:00:00.000

（所影响的行数为 4 行）

*/