SQL语句如何查询各个用户最长的连续登陆天数?

Java007 · · 628 次点击
mysql 8: create table Tmp_Data ( List_ID int , UID varchar(10), LoadTime datetime ); INSERT INTO Tmp_Data (UID,LoadTime) values ('201','2017/01/01'), ('202','2017/01/02'), ('202','2017/01/03'), ('203','2017/01/03' ), ('201','2017/01/04'), ('202','2017/01/04'), ('201','2017/01/05'), ('202','2017/01/05'), ('201','2017/01/06'), ('203','2017/01/06'), ('203','2017/01/07'); Select UID,max(cnt) as cnt From ( Select UID,Grp_No,count(*) as cnt From ( Select UID,LoadTime,(Day(LoadTime)-ROW_NUMBER() OVER (Partition By UID Order By UID,LoadTime)) as Grp_No From Tmp_Data ) a Group By UID,Grp_No ) a Group By UID; select version(); select * from Tmp_Data;
#2
更多评论
day函数取时间字段的天值 排序后相减 如果是连续登录 则相减的差值为同一个值 最后统计差值相同最大的值 则为最长连续登录时间
#1