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

一条有点挑战性的sql，关于sql求连续日数和连续日数的开始与结束日期的计算
从下列表中提取信息：
id dateTime data  
12011-08-10 04:06:00.0000
22011-08-10 05:06:00.0001
32011-09-11 01:00:00.0001
42011-09-12 02:00:00.0001
52011-10-12 23:53:00.0000
62011-10-12 23:53:00.0000
72011-10-12 23:53:00.0000
82011-10-12 23:53:00.0000
92011-10-12 23:53:00.0000
102011-10-12 23:53:00.0001
112011-10-12 23:53:00.0001`
122011-10-12 23:53:00.0001
132011-10-12 23:53:00.0001
142011-10-12 23:54:00.0001
152011-10-12 23:54:00.0001

问题：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 
 
[解决办法]
探讨


--> 测试数据：[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 ……