分享解决连续时间问题的方法

SQL code

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

http://topic.csdn.net/u/20120325/17/5a53bd46-8870-450a-a9ca-7ef8661e638d.html

SQL code

--当你看完处理连续数字的问题的解决方案时我相信也就明白了解决连续时间问题的方案，下面我以一种方法实现/*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*/-----------------------------希望能看到有人写出给多的方法哦。谢谢阅读

[解决办法]
感谢分享..

[解决办法]
回复一下，支持分享！
[解决办法]
支持。
[解决办法]

SQL code

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 行受影响) 
 
[解决办法]
探讨


引用:

回复一下，支持分享！


求推荐，木分了

[解决办法]
谢谢 分享 收藏了
[解决办法]
楼主威武！ 支持楼主！
[解决办法]
hah  谢谢楼主分享
[解决办法]
哇塞 好高深哦 - -  看不太懂！！！
[解决办法]
谢谢,分享,收藏!支持楼主！
[解决办法]
SQL code
--楼主的结果确实少一条？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*/
[解决办法]
探讨


SQL code

--楼主的结果确实少一条？
declare @T table (name varchar(2),logindate datetime)
insert into @T
select 'a1','2011-1-2' union all
select 'a1','2011-1-3' union all
select 'a1','2011-1-4' union all
select……

[解决办法]
探讨


引用:

引用:

SQL code

--楼主的结果确实少一条？
declare @T table (name varchar(2),logindate datetime)
insert into @T
select 'a1','2011-1-2' union all
select 'a1','2011-1-3' union all
……

[解决办法]
探讨


本人愚昧，遇到问题不会想简单方法，只会一步一步分析怎么达到目的，所以如果你觉得你的方法简单，愿意分享就贴出来，不愿意就别来那样说。另外，你的简单方法的确可以帮别人解决问题，但是我想很多人摸不着简单方法的思路吧！那这样对解决这样的问题有什么帮助？下次遇到了问题稍微一变化，又不会，那这样学习有价值吗？随我我觉得最好的解决一类问题的方法不是说看那个方法少些代码了，而是看哪个方法最具一般性，容易变通！个人观点


[解决办法]
学习，很不错哦

[解决办法]
谢谢分享
[解决办法]
关注下！
[解决办法]
oh year……
[解决办法]
oh year……
[解决办法]
感谢楼主，学习学习
[解决办法]
膜拜一下大作
------解决方案-------------------- 
 
实际意义

比如论坛，经常会计算连续登录天数，连续登录达到3天的，给若干积分，连续登录达到7天，给若干积分
现在淘宝，就有，连续登录几天，淘金币就多给一些

探讨

西的意在哪呢?主可否用?

[解决办法]
支持，感谢分享
[解决办法]
支持。支持。
[解决办法]
楼主辛苦 学习了~！！
[解决办法]
--承接‘孤岛方法’
;with 
A
as
(
select name,logindate,dateadd(dd,-row_number()over(partition by name order by logindate),logindate) as diffday 
from logintable
),
B 
as
(
select name,min(logindate) as start_day,max(logindate) as end_day,(datediff(dd,min(logindate),max(logindate))+1) as logindays
from A
group by name,diffday
)

select name,start_day,end_day,logindays
into tb
from
(
select name,start_day,end_day,logindays,row_number()over(partition by name order by logindays desc) as rin
from B 
  ) fin
 where fin.rin=1
    
  select * from tb

/*
name start_day end_day logindays
a0 2011-01-29 00:00:00 2011-01-30 00:00:00 2
a1 2011-01-27 00:00:00 2011-01-28 00:00:00 2
a10 2011-01-19 00:00:00 2011-01-20 00:00:00 2
a100 2011-01-27 00:00:00 2011-01-29 00:00:00 3
*/
[解决办法]
感谢分享，学习、收藏
[解决办法]
谢谢分享
[解决办法]
有关孤岛、间断问题，包括时间，大家可以看下《Microsoft SQL Server 2008技术内幕:T-SQL查询
》这本书，讲解的非常详细。
[解决办法]
支持下，顶起！
[解决办法]
LZ 威武
[解决办法]
多谢分享了。
[解决办法]
值得学习