分享解决连续时间有关问题的方法

--最近在论坛看到过很多关于解决连续时间问题的帖子。关于连续时间问题其实也可以归于孤岛问题。关于孤岛问题的解决方案我之前发表过一篇帖子，链接如下：

--当你看完处理连续数字的问题的解决方案时我相信也就明白了解决连续时间问题的方案，下面我以一种方法实现/*name logindatea1 2011-1-2a1 2011-1-3a1 2011-1-4a1 2011-1-7a1 2011-1-12a1 2011-1-13a1 2011-1-16a2 2011-1-7a2 2011-1-8a2 2011-1-10a2 2011-1-11a2 2011-1-13a2 2011-1-24---------------------------------------------我需要的结果是：name start_day end_day logindaysa1 2011-1-2 2011-1-4 3a2 2011-1-7 2011-1-8 2a2 2011-1-10 2011-1-11 2*/----------------------------------------------> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([name] varchar(2),[logindate] date)insert [tbl]select 'a1','2011-1-2' union allselect 'a1','2011-1-3' union allselect 'a1','2011-1-4' union allselect 'a1','2011-1-7' union allselect 'a1','2011-1-12' union allselect 'a1','2011-1-13' union allselect 'a1','2011-1-16' union allselect 'a2','2011-1-7' union allselect 'a2','2011-1-8' union allselect 'a2','2011-1-10' union allselect 'a2','2011-1-11' union allselect 'a2','2011-1-13' union allselect 'a2','2011-1-24'with t as(select [name],[logindate],(select min(b.[logindate]) from tbl b where b.[logindate]>=a.[logindate] and b.name=a.nameand not exists (select * from tbl cwhere c.[logindate]=dateadd(dd,1,b.[logindate]) and c.name=b.name)) as grpfrom tbl a),mas(select [name],MIN([logindate]) as start_day,MAX(grp) as end_dayfrom t group by grp,name)select *,(DATEDIFF(DD,start_day,end_day)+1) as logindays from m a where (DATEDIFF(DD,start_day,end_day)+1) in(select max(DATEDIFF(DD,start_day,end_day)+1) from m bwhere a.name=b.name)-------------------------/*name    start_day    end_day    logindaysa1    2011-01-02    2011-01-04    3a2    2011-01-07    2011-01-08    2a2    2011-01-10    2011-01-11    2*/-----------------------------希望能看到有人写出给多的方法哦。谢谢阅读

create table tb1([name] varchar(2),[logindate] datetime)insert tb1select 'a1','2011-1-2' union allselect 'a1','2011-1-3' union allselect 'a1','2011-1-4' union allselect 'a1','2011-1-7' union allselect 'a1','2011-1-12' union allselect 'a1','2011-1-13' union allselect 'a1','2011-1-16' union allselect 'a2','2011-1-7' union allselect 'a2','2011-1-8' union allselect 'a2','2011-1-10' union allselect 'a2','2011-1-11' union allselect 'a2','2011-1-13' union allselect 'a2','2011-1-24'godeclare @date datetimeselect @date = min(logindate) from tb1;with ach as(    select [name],logindate,id=row_number() over (partition by [name] order by logindate)    from tb1)select [name],min(logindate) mindate,max(logindate) maxdate,    datediff(dd,min(logindate),max(logindate)) dddatefrom achgroup by [name],datediff(dd,@date,logindate)-idorder by [name],mindatedrop table tb1/******************************name mindate                 maxdate                 dddate---- ----------------------- ----------------------- -----------a1   2011-01-02 00:00:00.000 2011-01-04 00:00:00.000 2a1   2011-01-07 00:00:00.000 2011-01-07 00:00:00.000 0a1   2011-01-12 00:00:00.000 2011-01-13 00:00:00.000 1a1   2011-01-16 00:00:00.000 2011-01-16 00:00:00.000 0a2   2011-01-07 00:00:00.000 2011-01-08 00:00:00.000 1a2   2011-01-10 00:00:00.000 2011-01-11 00:00:00.000 1a2   2011-01-13 00:00:00.000 2011-01-13 00:00:00.000 0a2   2011-01-24 00:00:00.000 2011-01-24 00:00:00.000 0(8 行受影响)