mysql当月连续签到天数统计 weir 2017-06-07 17:59:34.0 mysql 4888 SELECT lianxu_days FROM ( SELECT member_id, max(days) lianxu_days, min(login_day) start_date, max(login_day) end_date FROM (SELECT member_id, @cont_day := (CASE WHEN (@last_uid = member_id AND DATEDIFF(created_ts, @last_dt) = 1) THEN (@cont_day + 1) WHEN (@last_uid = member_id AND DATEDIFF(created_ts, @last_dt) < 1) THEN (@cont_day + 0) ELSE 1 END) AS days, (@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix, @last_uid := member_id, @last_dt := created_ts login_day FROM (SELECT member_id, DATE(create_time) created_ts FROM aurora_member_signin WHERE member_id = #{memberId} and create_time between DATE_ADD(curdate(),interval -day(curdate())+1 day) and date(date_add(now(), interval 1 day)) ORDER BY member_id, create_time) AS t, (SELECT @last_uid := '', @last_dt := '', @cont_ix := 0, @cont_day := 0) AS t1 ) AS t2 GROUP BY member_id, cont_ix ) tmp ORDER BY end_date DESC limit 1 只需要传入一个会员ID,返回的就是当月的到当前日期的连续签到天数,这里只显示最新的连续签到天数