一道难题，请大家解决 100分

Question

有一张登录表loginlog(userid,logintime),写出你觉得最优的存储过程，根据传入参数，输出某个用户在某段时间内连续登录的最大天数，传入参数为，@userid int,@logintimestart datetime,@logintimeend datetime.

Answer 1

declare @loginlog table (userid int,logintime datetime)
insert into @loginlog
select 1,'2010-03-01' union all
select 1,'2010-03-02' union all
select 1,'2010-03-05' union all
select 1,'2010-03-06' union all
select 1,'2010-03-07' union all
select 1,'2010-03-08' union all
select 2,'2010-03-01' union all
select 2,'2010-03-07' union all
select 2,'2010-03-10' union all
select 2,'2010-03-11' union all
select 2,'2010-03-13'
;with maco as (
select row_number() over (partition by userid order by logintime desc)
as rid, * from @loginlog)
select a.userid,最大连续天数=
sum(case when datediff(d,b.logintime,a.logintime)=-1 then 1 else 0 end )+1
 from maco a left join maco b on a.rid=b.rid+1 and a.userid=b.userid
group by a.userid
/*
userid      最大连续天数
----------- -----------
1           5
2           2
*/条件加上就很容易了。

Answer 2

上面写错了，2次连续被我加到一起了。
重写个：declare @loginlog table (userid int,logintime datetime)
insert into @loginlog
select 1,'2010-03-01' union all
select 1,'2010-03-02' union all
select 1,'2010-03-05' union all
select 1,'2010-03-06' union all
select 1,'2010-03-07' union all
select 1,'2010-03-08' union all
select 2,'2010-03-01' union all
select 2,'2010-03-07' union all
select 2,'2010-03-10' union all
select 2,'2010-03-11' union all
select 2,'2010-03-13'
;with maco as (
select row_number() over (partition by userid order by logintime desc)
as rid, * from @loginlog)
,maco2 as (
select row_number() over (partition by a.userid order by a.logintime desc) as nid,
a.rid,a.userid,a.logintime,b.logintime as blogintime
 from maco a left join maco b on a.rid=b.rid+1 and a.userid=b.userid
where datediff(d,b.logintime,a.logintime)=-1)
select userid,最大连续数=max(mcount)+1 from 
(select userid,mcount=count(*) from maco2 group by userid,rid-nid) aa group by userid/*
userid      最大连续数
----------- -----------
1           4
2           2
*/

Answer 3

你的好像算了不对
select 1,'2010-03-05' union all
select 1,'2010-03-06' union all
select 1,'2010-03-07' union all
select 1,'2010-03-08' union all应该是4天吧，你怎么算出来是5天？

Answer 4

还有能不能做到最优化，性能上最好，而不是光去实现这个SQL语句。

Answer 5

declare @loginlog table (userid int,logintime datetime)
insert into @loginlog
select 1,'2010-03-01' union all
select 1,'2010-03-02' union all
select 1,'2010-03-05' union all
select 1,'2010-03-06' union all
select 1,'2010-03-07' union all
select 1,'2010-03-08' union all
select 2,'2010-03-01' union all
select 2,'2010-03-07' union all
select 2,'2010-03-10' union all
select 2,'2010-03-11' union all
select 2,'2010-03-13'
 
select rn=identity(int),* into #t from @loginlog order by userid,logintimeselect userid,max(mucount) 最大连续数 from 
(
    select userid,count(1) mucount from #t t
    where exists(select 1 from #t where userid=t.userid 
    and (logintime=t.logintime-1 or t.logintime=logintime-1))
    group by userid,logintime-rn
) as t group by useriddrop table #t
/*
userid      最大连续数
----------- -----------
1           4
2           2
*/

Answer 6

create  table loginlog(userid int,logintime datetime)
insert into loginlog
select 1,'2010-03-01' union all
select 1,'2010-03-02' union all
select 1,'2010-03-05' union all
select 1,'2010-03-06' union all
select 1,'2010-03-07' union all
select 1,'2010-03-08' union all
select 2,'2010-03-01' union all
select 2,'2010-03-07' union all
select 2,'2010-03-10' union all
select 2,'2010-03-11' union all
select 2,'2010-03-13'--放入临时表
select *,identity(int,1,1) as [ID] into #temp from loginlog--userid=1
select top 1 b.logintime,a.logintime,a.id-b.id+1 as 天数
from #temp a,#temp b
where a.id>b.id and a.userid=b.userid
      and datediff(dd,b.logintime,a.logintime)=a.id-b.id
and a.userid=1
order by a.id-b.id desc
/*
logintime                 logintime               天数          
------------------------------------------ ----------- 
2010-03-05 00:00:00.000   2010-03-08 00:00:00.000   4（所影响的行数为 1 行）
*/--userid=2
select top 1 b.logintime,a.logintime,a.id-b.id+1 as 天数
from #temp a,#temp b
where a.id>b.id and a.userid=b.userid
      and datediff(dd,b.logintime,a.logintime)=a.id-b.id
and a.userid=2
order by a.id-b.id desc
/*
logintime                    logintime              天数          
----------------------------------------------------------- 
2010-03-10 00:00:00.000    2010-03-11 00:00:00.000     2（所影响的行数为 1 行）*/
drop table #temp,loginlog

Answer 7

create table #loginlog(userid int,logintime datetime)
insert #loginlog 
select 1 , '2011-06-30' union all
select 1 , '2011-06-29' union all
select 1 , '2011-06-28' union all
select 1 , '2011-06-27' union all
select 1, '2011-06-25' union all
select 1 , '2011-06-24' union all
select 1 , '2011-06-21' union all
select 1 , '2011-06-20' union all
select 1 , '2011-06-18' union allselect 2 , '2011-06-03' union all
select 2 , '2011-06-02' union all
select 2 , '2011-06-01' union all
select 2 , '2011-05-31' union all
select 2 , '2011-05-30' union all
select 2 , '2011-04-24' union all
select 2 , '2011-04-23' union all
select 2 , '2011-04-22' union all
select 2 , '2011-04-20' 
create proc getMaxloginlogCount
@userid int,
@logintimestart datetime,
@logintimeend datetime
as
begin
   ;with TempA as (select l1.*,
                     datediff(day,'1900-01-01',logintime)-
                     Row_number()over(partition by userid order by logintime) as num 
                   from #loginlog as l1
                   where (@userid='' or (@userid<>'' and l1.userid=@userid)) and 
                         (@logintimestart='' or (@logintimestart<>'' and l1.logintime>=@logintimestart)) and 
                         (@logintimeend='' or (@logintimeend<>'' and l1.logintime<=@logintimeend)))
   select top 1 count(1) as MaxloginlogCount from TempA group by num order by count(1) desc
endexec getMaxloginlogCount 2,'2011-04-24',''