签到表:
需求:统计连续签到的 用户
1.根据用户和日期分组
select user_name, sign_date from user_sign group by user_name, sign_date order by user_name, sign_date
2.取出行号
select user_name, sign_date, if(@tmp = user_name, @rownum := @rownum + 1, @rownum := 1) as num, @tmp := user_name from (select user_name, sign_date from user_sign group by user_name, sign_date order by user_name, sign_date) a, (select @rownum := 0, @tmp := ‘‘) b ;
3.使用日期做对比,有则叠加,没有则不叠加
datediff:返回2个日期之间的间隔天数
select user_name, sign_date, if(@tmp = user_name and datediff(sign_date, @date) = 1, @rownum := @rownum + 1, @rownum := 1) as num, @tmp := user_name, @date := sign_date from (select user_name, sign_date from user_sign group by user_name, sign_date order by user_name, sign_date) a, (select @rownum := 0, @tmp := ‘‘, @date := ‘‘) b
4.取出连续签到4天的用户列表
select * from ( select user_name, sign_date, if(@tmp = user_name and datediff(sign_date, @date) = 1, @rownum := @rownum + 1, @rownum := 1) as num, @tmp := user_name, @date := sign_date from (select user_name, sign_date from user_sign group by user_name, sign_date order by user_name, sign_date) a, (select @rownum := 0, @tmp := ‘‘, @date := ‘‘) b ) c where num=4;
原文：https://www.cnblogs.com/8013-cmf/p/11175061.html