学步园

最近遇到不少关于解决连续登陆天数的帖子。这类问题实际也就是我们经常遇到的孤岛问题的一个变种
解决这类问题，我们有一个最基本的思路：
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
*/
关于连续登陆问题也就是时间孤岛问题，所以我们的解决思路就等同于求孤岛。
在学习的过程中我建议大家能抓住最基本的方法，不要一味的追求最简单的方法。
任何一个简单的方法都是建立在一定得基础知识和熟练程度上的，只有当你熟练
掌握了最基本的东西才可能去发现更简单的办法。


谢谢各位阅读