MySQL 查询连续登陆天数大于7天的用户

-- MySQL 查询连续登陆天数大于7天的用户
SELECT
	username,
	max(days) continue_days,
	min(login_date) start_date,
	max(login_date) end_date
FROM
	(
		SELECT
			username,
			@continue_day := (
				CASE
				WHEN (
					@last_uid = username
					AND DATEDIFF(loginDate, @last_date) = 1
				) THEN
					(@continue_day + 1)
				WHEN (
					@last_uid = username
					AND DATEDIFF(loginDate, @last_date) < 1
				) THEN
					(@continue_day + 0)
				ELSE
					1
				END
			) AS days,
			(
				@continue_index := (
					@continue_index +
					IF (@continue_day = 1, 1, 0)
				)
			) AS cont_ix,
			@last_uid := username,
			@last_date := loginDate login_date
		FROM
			(
				SELECT
					username,
					DATE(create_date) loginDate
				FROM
					user_login_info
				WHERE
					1 = 1 -- AND username = 'xxxxxx' -- 查询指定登陆人 xxxxxx
				ORDER BY
					username,
					create_date
			) AS t,
			(
				SELECT
					@last_uid := '',
					@last_date := '',
					@continue_index := 0,
					@continue_day := 0
			) AS t1
	) AS t2
GROUP BY
	username,
	cont_ix
HAVING
	-- 连续登陆天数 7
	continue_days > 7;