如何查询出连续登陆的最长天数

 最近遇到不少关于解决连续登陆天数的帖子。这类问题实际也就是我们经常遇到的孤岛问题的一个变种  
解决这类问题，我们有一个最基本的思路：  
step 1:找出间断之后的点，为他们分配行号（这是孤岛的起点）  
step 2:找出间断之前的点，为他们分配行号（这是孤岛的终点）  
step 3:以行号相等作为条件，匹配孤岛的起点和终点  
在实现以上三步后，我们基本上就能解决这类问题了，一下我以三种方法演示：  
 
/*  
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'  
--方法1  
;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)  
---------------------------------------------------------------------------  
---------------------------------------------------------------------------  
--方法2  
declare @date datetime  
select @date = min(logindate) from tbl  
;with ach as  
(  
select [name],logindate,  
id=row_number() over (partition by [name] order by logindate)  
from tbl  
),  
t as(  
select [name],min(logindate) mindate,max(logindate) maxdate,  
(datediff(dd,min(logindate),max(logindate))+1) dddate   
from ach  
group by [name],datediff(dd,@date,logindate)-id  
--order by [name],mindate  
)  
select * from t a where dddate in(select max(dddate) from t b where  
a.name=b.name)  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--方法3  
;with t as  
(  
select name,[logindate],dateadd(dd,  
-row_number()over(partition by name   
order by [logindate]),[logindate]) as diff from tbl  
),  
m as(  
select name,min([logindate]) as start_day,max([logindate]) as end_day,   
(datediff(dd,min([logindate]),max([logindate]))+1) as logindays  
from t  
group by name,diff  
)  
select * from m a   
where logindays in(select MAX(logindays) 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-10  2011-01-11  2  
a2  2011-01-07  2011-01-08  2  
*/  
关于连续登陆问题也就是时间孤岛问题，所以我们的解决思路就等同于求孤岛。  
在学习的过程中我建议大家能抓住最基本的方法，不要一味的追求最简单的方法。  
任何一个简单的方法都是建立在一定得基础知识和熟练程度上的，只有当你熟练  

掌握了最基本的东西才可能去发现更简单的办法。 

来自：http://blog.csdn.net/travylee/article/details/7460030