求接续操作（登录）数量（次数）最大的记录（用户）_Sql Server

求连续操作（登录）数量（次数）最大的记录（用户）

昨晚上老同事聚会，一个同事说道一个面试问题没有一个人做出来，就是求连续日期登录次数最大的用户，同事说借助 rownumber即可求解，由于是喝酒聊天，也没有说详细的解决过程。今天早上想了下，终于想到了具体的解决思路。

登录时间里面有详细的时分秒数据，而我们的题目只要求连续的天数，所以使用DATEDIFF函数可以解决，

DATEDIFF(d,LoginTime,getdate()) as diffDate ,

有多个用户都在登录，因此应该以用户名为分区，登录时间为顺序来计算rownumber，因此，就是下面的表达式：

ROW_NUMBER() over(partition by Name order by LoginTime desc) as rn

关键问题来了，如何求得连续的登录情况？

如果是连续的记录，那么 diffDate- rn 肯定是相同的！

OK，果然这种方式很巧妙，那么我们最终的SQL写出来也不难了。

开始动手，先构造一个表，插入初始数据：

/*  求连续登录次数最多的用户*/create table UserLoginInfo(  ID int IDENTITY primary key,  Name varchar(50) not null,  LoginTime datetime not null  )  go    insert UserLoginInfo (Name,LoginTime)  values('zhang','2015-11-10 12:01:50')  ,('li','2015-11-11 11:01:50')  ,('wang','2015-11-9 11:01:50');  go    insert UserLoginInfo (Name,LoginTime) values   ('zhang','2015-11-11 12:01:50'),  ('li','2015-11-11 12:01:50'),  ('wang','2015-11-11 11:01:50'),    ('zhang','2015-11-12 12:01:50'),  ('li','2015-11-13 13:01:50'),  ('wang','2015-11-12 11:01:50'),    ('zhang','2015-11-13 12:01:50'),  ('li','2015-11-14 11:01:50'),  ('wang','2015-11-14 11:01:50');  go

然后用下面的SQL得到结果：

select top 1        Name,diffRn,COUNT(diffRn)as LoginCount from(    select Name,diffDate,rn, (diffDate-rn) as diffRn     from(                select  ID,Name,                   DATEDIFF(d,LoginTime,getdate()) as diffDate ,                   ROW_NUMBER() over(partition by Name order by LoginTime desc) as rn          from UserLoginInfo    ) t1) t2group by diffRn,Name order by LoginCount desc

答案是：

Name    diffRn    LoginCountzhang    14    4

如果注释掉 top 1，我们就知道这个结果的由来了：

Name    diffRn    LoginCountzhang    14    4li    13    3wang    14    2wang    15    1li    14    1wang    13    1

这个问题也可以衍生出求连续登录的用户，或者求连续登录15天的用户（比如QQ的签到功能），是不是很熟悉呢？

实际上，上面这个查询，遇到一天登录多次的情况下，统计是不准确的，例如，构造下面的测试数据：

insert UserLoginInfo (Name,LoginTime) values      ('zhang'    ,'2015-11-10 12:01:50')    ,('li'            ,'2013-10-05 11:01:50')    ,('li'            ,'2013-10-06 11:01:50')    ,('li'            ,'2014-10-05 11:01:50')    ,('li'            ,'2014-10-06 11:01:50')    ,('li'            ,'2015-10-05 11:01:50')    ,('li'            ,'2015-10-06 11:01:50')    ,('li'            ,'2015-11-10 11:01:50')    ,('li'            ,'2015-11-11 11:01:50')    ,('wang'        ,'2015-11-09 11:01:50')    ,('zhang'        ,'2015-11-11 12:01:50')    ,('li'            ,'2015-11-11 12:01:50')    ,('wang'        ,'2015-11-11 11:01:50')    ,('zhang'        ,'2015-11-12 12:01:50')    ,('li'            ,'2015-11-13 13:01:50')    ,('wang'        ,'2015-11-12 11:01:50')    ,('zhang'        ,'2015-11-13 12:01:50')    ,('li'            ,'2015-11-14 11:01:50')    ,('wang'        ,'2015-11-14 11:01:50')    ;

View Code

这时应该先去除某天的重复数据，才是正确的，所以查询应该做如下改进：

select --top 1        Name,diffRn,COUNT(diffRn)as LoginCount from(    select Name,diffDate,rn, (diffDate-rn) as diffRn     from(                select  Name,                   diffDate,                  ROW_NUMBER() over(partition by Name order by diffDate asc) rn          from (                 select distinct Name,DATEDIFF(d,LoginTime,getdate()) as diffDate                 from  UserLoginInfo           ) t0    ) t1) t2group by diffRn,Name order by LoginCount desc;

结果是：

Name    diffRn    LoginCountzhang    14    4wang    14    2li    13    2li    14    2li    48    2li    411    2li    774    2wang    13    1wang    15    1

结果符合我们的预期，算是完整的答案了。

不知道别的同学还没有更好的解决方案？

-------------------------------------------

PS：如果你经常会在程序中写这样复杂的SQL，推荐你使用PDF.NET SOD框架的SQL-MAP功能，将SQL写在配置文件中，集中管理，并且方便跨数据库移植。

SOD框架 PDF.NET_SOD Ver 5.3.6.1125 已经发布，喜欢的朋友可以下载当前这个最新的稳定版本，有问题，可以加框架的QQ群：PDF.NET SOD高级群 18215717

1楼枕边书: 以前想过这个问题，我觉得每个月设置一个是否登陆的31位flag，用其二进制位来存储当天是否登陆，如果登陆就拿dateamp;flag，这样，如果需要求这登陆天数值的话，就用一下位运算，其他值也类似。二进制运算飞快，只是有复杂需求的话逻辑有点麻烦。