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

--当你看完处理连续数字的问题的解决方案时我相信也就明白了解决连续时间问题的方案，下面我以一种方法实现
/*
name logindate
a1 2011-1-2
a1 2011-1-3
a1 2011-1-4
a1 2011-1-7
a1 2011-1-12
a1 2011-1-13
a1 2011-1-16
a2 2011-1-7
a2 2011-1-8
a2 2011-1-10
a2 2011-1-11
a2 2011-1-13
a2 2011-1-24
---------------------------------------------
我需要的结果是：
name start_day end_day logindays
a1 2011-1-2 2011-1-4 3
a2 2011-1-7 2011-1-8 2
a2 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 all
select 'a1','2011-1-3' union all
select 'a1','2011-1-4' union all
select 'a1','2011-1-7' union all
select 'a1','2011-1-12' union all
select 'a1','2011-1-13' union all
select 'a1','2011-1-16' union all
select 'a2','2011-1-7' union all
select 'a2','2011-1-8' union all
select 'a2','2011-1-10' union all
select 'a2','2011-1-11' union all
select 'a2','2011-1-13' union all
select '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.name
and not exists (select * from tbl c
where c.[logindate]=dateadd(dd,1,b.[logindate]) and c.name=b.name)) as grp
from tbl a
),m
as(
select [name],MIN([logindate]) as start_day,MAX(grp) as end_day
from 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 b
where a.name=b.name)
-------------------------
/*
name    start_day    end_day    logindays
a1    2011-01-02    2011-01-04    3
a2    2011-01-07    2011-01-08    2
a2    2011-01-10    2011-01-11    2
*/
-----------------------------
希望能看到有人写出给多的方法哦。谢谢阅读

create table tb1([name] varchar(2),[logindate] datetime)
insert tb1
select 'a1','2011-1-2' union all
select 'a1','2011-1-3' union all
select 'a1','2011-1-4' union all
select 'a1','2011-1-7' union all
select 'a1','2011-1-12' union all
select 'a1','2011-1-13' union all
select 'a1','2011-1-16' union all
select 'a2','2011-1-7' union all
select 'a2','2011-1-8' union all
select 'a2','2011-1-10' union all
select 'a2','2011-1-11' union all
select 'a2','2011-1-13' union all
select 'a2','2011-1-24'
go

declare @date datetime
select @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)) dddate
from ach
group by [name],datediff(dd,@date,logindate)-id
order by [name],mindate

drop table tb1

/******************************

name mindate                 maxdate                 dddate
---- ----------------------- ----------------------- -----------
a1   2011-01-02 00:00:00.000 2011-01-04 00:00:00.000 2
a1   2011-01-07 00:00:00.000 2011-01-07 00:00:00.000 0
a1   2011-01-12 00:00:00.000 2011-01-13 00:00:00.000 1
a1   2011-01-16 00:00:00.000 2011-01-16 00:00:00.000 0
a2   2011-01-07 00:00:00.000 2011-01-08 00:00:00.000 1
a2   2011-01-10 00:00:00.000 2011-01-11 00:00:00.000 1
a2   2011-01-13 00:00