求登录时长 ### DATEDIFF(d,LoginTime,getdate()) as diffDate ,
计算登录次数,登录时间为顺序来计算rownumber
### ROW_NUMBER() over(partition by Name order by LoginTime desc) as rn
合并起来
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