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

2020-02-14 21:22栏目:竞技宝app
TAG:

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

--当你看完处理连续数字的问题的解决方案时我相信也就明白了解决连续时间问题的方案,下面我以一种方法实现/*namelogindatea12011-1-2a12011-1-3a12011-1-4a12011-1-7a12011-1-12a12011-1-13a12011-1-16a22011-1-7a22011-1-8a22011-1-10a22011-1-11a22011-1-13a22011-1-24---------------------------------------------我需要的结果是:namestart_dayend_daylogindaysa12011-1-22011-1-43a22011-1-72011-1-82a22011-1-102011-1-112*/----------------------------------------------测试数据:[tbl]ifobject_id('[tbl]')isnotnulldroptable[tbl]createtable[tbl]([name]varchar(2),[logindate]date)insert[tbl]select'a1','2011-1-2'unionallselect'a1','2011-1-3'unionallselect'a1','2011-1-4'unionallselect'a1','2011-1-7'unionallselect'a1','2011-1-12'unionallselect'a1','2011-1-13'unionallselect'a1','2011-1-16'unionallselect'a2','2011-1-7'unionallselect'a2','2011-1-8'unionallselect'a2','2011-1-10'unionallselect'a2','2011-1-11'unionallselect'a2','2011-1-13'unionallselect'a2','2011-1-24'withtas(select[name],[logindate],(selectmin(b.[logindate])fromtblbwhereb.[logindate]=a.[logindate]andb.name=a.nameandnotexists(select*fromtblcwherec.[logindate]=dateadd(dd,1,b.[logindate])andc.name=b.name))asgrpfromtbla),mas(select[name],MIN([logindate])asstart_day,MAX(grp)asend_dayfromtgroupbygrp,name)select*,(DATEDIFF(DD,start_day,end_day)+1)aslogindaysfrommawhere(DATEDIFF(DD,start_day,end_day)+1)in(selectmax(DATEDIFF(DD,start_day,end_day)+1)frommbwherea.name=b.name)-------------------------------------------------------------/*namestart_dayend_daylogindaysa12011-01-022011-01-043a22011-01-072011-01-082a22011-01-102011-01-112*/-----------------------------------------------------------------希望能看到有人写出给多的方法哦。谢谢阅读

版权声明:本文由龙竞技官网发布于竞技宝app,转载请注明出处:分享解决连续时间问题的方法