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

一条有点挑战性的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)goinsert [test]select 1,'2011-08-10 04:06:00.000',0 union allselect 2,'2011-08-10 05:06:00.000',1 union allselect 3,'2011-09-11 01:00:00.000',1 union allselect 4,'2011-09-12 02:00:00.000',1 union allselect 5,'2011-10-12 23:53:00.000',0 union allselect 6,'2011-10-12 23:53:00.000',0 union allselect 7,'2011-10-12 23:53:00.000',0 union allselect 8,'2011-10-12 23:53:00.000',0 union allselect 9,'2011-10-12 23:53:00.000',0 union allselect 10,'2011-10-12 23:53:00.000',1 union allselect 11,'2011-10-12 23:53:00.000',1 union allselect 12,'2011-10-12 23:53:00.000',1 union allselect 13,'2011-10-12 23:53:00.000',1 union allselect 14,'2011-10-12 23:54:00.000',1 union allselect 15,'2011-10-12 23:54:00.000',1goselect * from test awhere (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    13    2011-09-11 01:00:00.000    14    2011-09-12 02:00:00.000    110    2011-10-12 23:53:00.000    111    2011-10-12 23:53:00.000    112    2011-10-12 23:53:00.000    113    2011-10-12 23:53:00.000    114    2011-10-12 23:54:00.000    115    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=1union allselect 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.rdrop table #c