如何查看连续几天操作数量的最大记录

题目：求连续日期登录次数最大的用户

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

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

ROW_NUMBER() over(partition by Name order by LoginTime desc) as rn
关键问题来了，如何求得连续的登录情况？

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

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

复制代码
/*
求连续登录次数最多的用户
*/
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
) t2
group by diffRn,Name
order by LoginCount desc
复制代码
答案是：

Name diffRn LoginCount
zhang 14 4

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

复制代码
Name diffRn LoginCount
zhang 14 4
li 13 3
wang 14 2
wang 15 1
li 14 1
wang 13 1
复制代码

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

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

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
) t2
group by diffRn,Name
order by LoginCount desc;
复制代码
结果是：

复制代码
Name diffRn LoginCount
zhang 14 4
wang 14 2
li 13 2
li 14 2
li 48 2
li 411 2
li 774 2
wang 13 1
wang 15 1
复制代码
结果符合我们的预期，算是完整的答案了。

---

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