MySQL5.7统计连续超过3天提交了任务的用户

需求:统计表中连续超过3天都提交了任务的用户名

SELECT rn AS '连续提交任务天数', submit_creator AS '提交人' FROM
(
    SELECT
        @rn:= CASE
            -- 当前记录的用户名等于上一条记录的用户名 AND 当前记录的日期与上一条记录的日期之差为1
            WHEN @pre_submit_creator=creator AND DATEDIFF(@pre_submit_date,submit_date) = 1
            THEN @rn + 1
            ELSE 1 END AS rn,
        @pre_submit_date:=submit_date AS submit_date,
        @pre_submit_creator:=creator AS submit_creator
    FROM
        (
            -- 日期降序并去重
            SELECT DISTINCT DATE(gmt_create) AS submit_date, creator FROM task_log
            ORDER BY DATE(gmt_create) DESC
        )    AS log,
    (SELECT @rn=0, @submit_creator=null, @submit_date=null) AS b
) log
where rn >= 3;