分享解决连续时间问题的方法
--最近在论坛看到过很多关于解决连续时间问题的帖子。关于连续时间问题其实也可以归于孤岛问题。关于孤岛问题的解决方案我之前发表过一篇帖子，链接如下：
--当你看完处理连续数字的问题的解决方案时我相信也就明白了解决连续时间问题的方案，下面我以一种方法实现/*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 行受影响)
[解决办法]
--楼主的结果确实少一条？declare @T table (name varchar(2),logindate datetime)insert into @Tselect '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 maco as(select row_number () over (partition by name order by logindate-getdate()) as no, datediff(d,getdate(),logindate) as num,* from @T)select name, convert(varchar(10),min(logindate),120) as start_day, convert(varchar(10),max(logindate),120) as end_day, datediff(d,min(logindate),max(logindate)) +1 as logindaysfrom macogroup by name,num-no having(min(logindate)<>max(logindate))order by 1/*name start_day end_day logindays---- ---------- ---------- -----------a1 2011-01-02 2011-01-04 3a1 2011-01-12 2011-01-13 2a2 2011-01-07 2011-01-08 2a2 2011-01-10 2011-01-11 2*/
[解决办法]