一条有些挑战性的sql，关于sql求连续日数和连续日数的开始与结束日期的计算

一条有点挑战性的sql，关于sql求连续日数和连续日数的开始与结束日期的计算
从下列表中提取信息：
id dateTime data
1 2011-08-10 04:06:00.000 0
2 2011-08-10 05:06:00.000 1
3 2011-09-11 01:00:00.000 1
4 2011-09-12 02:00:00.000 1
5 2011-10-12 23:53:00.000 0
6 2011-10-12 23:53:00.000 0
7 2011-10-12 23:53:00.000 0
8 2011-10-12 23:53:00.000 0
9 2011-10-12 23:53:00.000 0
10 2011-10-12 23:53:00.000 1
11 2011-10-12 23:53:00.000 1`
12 2011-10-12 23:53:00.000 1
13 2011-10-12 23:53:00.000 1
14 2011-10-12 23:54:00.000 1
15 2011-10-12 23:54:00.000 1

问题：1.提取data中连续为1的日期共几天（显示出来的结果为累年中这个月的最大值）
2.显示累年中最大值得开始日期和结束日期
要求：效率要高
显示后的结果如下图所见：
┏━━━━┯━━━━━━━━━━━━━━
┃ 月 │ 1 2
┃总日数 │ 66 38
┃ 起始日 │ 2013-11-9 2014-12-31
┃ 终止日 │ 2014-1-13 2015-2-6
求各位高手指教

------解决方案--------------------

SQL code


--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[id] int,
[dateTime] datetime,
[data] int
)
go
insert [test]
select 1,'2011-08-10 04:06:00.000',0 union all
select 2,'2011-08-10 05:06:00.000',1 union all
select 3,'2011-09-11 01:00:00.000',1 union all
select 4,'2011-09-12 02:00:00.000',1 union all
select 5,'2011-10-12 23:53:00.000',0 union all
select 6,'2011-10-12 23:53:00.000',0 union all
select 7,'2011-10-12 23:53:00.000',0 union all
select 8,'2011-10-12 23:53:00.000',0 union all
select 9,'2011-10-12 23:53:00.000',0 union all
select 10,'2011-10-12 23:53:00.000',1 union all
select 11,'2011-10-12 23:53:00.000',1 union all
select 12,'2011-10-12 23:53:00.000',1 union all
select 13,'2011-10-12 23:53:00.000',1 union all
select 14,'2011-10-12 23:54:00.000',1 union all
select 15,'2011-10-12 23:54:00.000',1
go
select * from test a
where (exists(select 1 from test b where a.id=b.id+1 and b.data=1)
or exists(select 1 from test c where a.id=c.id-1 and c.data=1))
and a.data=1
/*
id    dateTime    data
------------------------------------
2    2011-08-10 05:06:00.000    1
3    2011-09-11 01:00:00.000    1
4    2011-09-12 02:00:00.000    1
10    2011-10-12 23:53:00.000    1
11    2011-10-12 23:53:00.000    1
12    2011-10-12 23:53:00.000    1
13    2011-10-12 23:53:00.000    1
14    2011-10-12 23:54:00.000    1
15    2011-10-12 23:54:00.000    1
*/

--把这个连续为1的给你筛选出来了，至于你说的累年什么的不懂。

------解决方案--------------------

--> 测试数据：[test]
if object_id('[test]') is not null  
drop table [test]
create table [test](
[id] int,
[dateTime] datetime,
[data] int
)
go
insert [test]
select 1,'2011-08-10 04:06:00.000',0 union all
select 2,'2011-08-10 05:06:00.000',1 union all
select 3,'2011-09-11 01:00:00.000',1 union all
select 4,'2011-09-12 02:00:00.000',1 union all
select 5,'2011-10-12 23:53:00.000',0 union all
select 6,'2011-10-12 23:53:00.000',0 union all
select 7,'2011-10-12 23:53:00.000',0 union all
select 8,'2011-10-12 23:53:00.000',0 union all
select 9,'2011-10-12 23:53:00.000',0 union all
select 10,'2011-10-12 23:53:00.000',1 union all
select 11,'2011-10-12 23:53:00.000',1 union all
select 12,'2011-10-12 23:53:00.000',1 union all
select 13,'2011-10-12 23:53:00.000',1 union all
select 14,'2011-10-12 23:54:00.000',1 union all
select 15,'2011-10-12 23:54:00.000',1
go

SQL code


 
 
;with  cte as(
select *, case when data=1 then 1 else 0 end as r from [test]   where id=1
union all
select t.*,
              case 
              when t.data=1 and c.r=0 then 1
              when t.data=1 and c.r>0 then abs(c.r)
              when t.data=0 and c.r>0 then -(c.r)
              when t.data=0 and c.r<0 then c.r
              when t.data=1 and c.r<0 then abs(c.r)+1
              else 0 end as r  
      from cte c,test t where t.id=c.id+1
)
select *into #c from cte where r>0
;with maxdate as(
select max(datetime) as datetime,r from #c group by r),
     mindate as(
select min(datetime) as datetime,r from #c group by r)
select i.datetime as 最小时间,
       a.datetime as 最大时间,
       datediff(dd,i.datetime,a.datetime) as 天数,
       datediff(mm,i.datetime,a.datetime) as 月数 from maxdate a,
       mindate i where i.r=a.r
drop table #c

一条有些挑战性的sql，关于sql求连续日数和连续日数的开始与结束日期的计算

相关推荐