mysql 5.7 查询最大最小连续签到时间

创建record表：

CREATE TABLE `record` (
  `user_id` int(11) NOT NULL,
  `date` timestamp NULL DEFAULT NULL,
  `type` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

插入数据：

INSERT INTO record (user_id, `date`, `type`) VALUES(1, '2020-09-22 11:22:03', '1');
INSERT INTO record (user_id, `date`, `type`) VALUES(1, '2020-09-23 11:22:03', '1');
INSERT INTO record (user_id, `date`, `type`) VALUES(1, '2020-09-23 11:22:03', '2');
INSERT INTO record (user_id, `date`, `type`) VALUES(1, '2020-09-24 11:22:03', '1');
INSERT INTO record (user_id, `date`, `type`) VALUES(1, '2020-09-26 11:22:03', '1');
INSERT INTO record (user_id, `date`, `type`) VALUES(1, '2020-09-27 11:22:03', '2');
INSERT INTO record (user_id, `date`, `type`) VALUES(1, '2020-09-28 11:22:03', '1');
INSERT INTO record (user_id, `date`, `type`) VALUES(1, '2020-09-28 12:22:03', '1');
INSERT INTO record (user_id, `date`, `type`) VALUES(2, '2020-09-22 11:22:03', '1');
INSERT INTO record (user_id, `date`, `type`) VALUES(2, '2020-09-23 11:22:03', '1');
INSERT INTO record (user_id, `date`, `type`) VALUES(2, '2020-09-26 11:22:03', '1');
INSERT INTO record (user_id, `date`, `type`) VALUES(2, '2020-09-27 11:22:03', '2');
INSERT INTO record (user_id, `date`, `type`) VALUES(2, '2020-09-28 11:22:03', '1');
INSERT INTO record (user_id, `date`, `type`) VALUES(2, '2020-09-28 12:22:03', '1');
INSERT INTO record (user_id, `date`, `type`) VALUES(3, '2020-09-28 12:22:03', '2');
INSERT INTO record (user_id, `date`, `type`) VALUES(4, '2020-09-28 12:22:03', '1');

sql1:

select
	*
from
	(
	select
		*
	from
		(
		select
			user_id,
			max(days) lianxu_days_max,
			min(login_day) start_date,
			max(login_day) end_date
		from
			(
			select
				user_id,
				@cont_day :=
				(case
					when (@last_uid = user_id
					and DATEDIFF(date, @last_dt) = 1) then (@cont_day + 1)
					when (@last_uid = user_id
					and DATEDIFF(date, @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 := user_id,
				@last_dt := date login_day
			from
				(
				select
					user_id,
					DATE(date) date
				from
					record
				where
					type = '1'
				order by
					user_id,
					date) as t,
				(
				select
					@last_uid := '',
					@last_dt := '',
					@cont_ix := 0,
					@cont_day := 0) as t1 ) as t2
		group by
			user_id,
			cont_ix
		having
			lianxu_days_max >= 0 ) tmp
	order by
		lianxu_days_max desc) ntmp
group by
	user_id

sql2:

select
	user_id,
	max(lianxu_days),
	min(lianxu_days),
	start_date,
	end_date
from
	(
	select
		user_id,
		max(days) lianxu_days,
		min(login_day) start_date,
		max(login_day) end_date
	from
		(
		select
			user_id,
			@cont_day :=
			(case
				when (@last_uid = user_id
				and DATEDIFF(date, @last_dt) = 1) then (@cont_day + 1)
				when (@last_uid = user_id
				and DATEDIFF(date, @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 := user_id last_user_id,
			@last_dt := date login_day
		from
			(
			select
				user_id,
				DATE(date) date
			from
				record
			where
				type = '1'
			order by
				user_id,
				date) as t,
			(
			select
				@last_uid := '',
				@last_dt := '',
				@min := 0,
				@cont_ix := 0,
				@cont_day := 0) as t1) t2
	group by
		user_id,
		cont_ix
	order by
		lianxu_days desc) as tmp
group by
	user_id