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

最新推荐文章于 2023-11-03 08:18:32 发布

永生天地

最新推荐文章于 2023-11-03 08:18:32 发布

阅读量1.4k

点赞数

分类专栏： [Microsoft] - [sqlserver] 文章标签： 2010 table insert object null sql

本文链接：https://blog.csdn.net/xys_777/article/details/5683641

版权

[Microsoft] - [sqlserver] 专栏收录该内容

114 篇文章 0 订阅

订阅专栏

--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 行）