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

最近遇到不少关于解决连续登陆天数的帖子。这类问题实际也就是我们经常遇到的孤岛问题的一个变种

step 1:找出间断之后的点，为他们分配行号（这是孤岛的起点）
step 2:找出间断之前的点，为他们分配行号（这是孤岛的终点）
step 3:以行号相等作为条件，匹配孤岛的起点和终点

/*
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
---------------------------------------------

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]
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(
and not exists (select * from tbl c
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
(
id=row_number() over (partition by [name] order by logindate)
from tbl
),
t as(
from ach
--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
(
-row_number()over(partition by name
),
m as(
from t
group by name,diff
)
select * from m a
where a.name=b.name)
/*
a1	2011-01-02	2011-01-04	3
a2	2011-01-10	2011-01-11	2
a2	2011-01-07	2011-01-08	2
*/


08-26 6628

04-27
07-12 1573
08-15 1万+
09-04 4633
05-22 145