MySQL 三天连续登录SQL

创建测试数据：

USE `test`;
CREATE TABLE `login_t` (
  `login_id` varchar(20) DEFAULT NULL,
  `login_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into `login_t`(`login_id`,`login_date`) values ('001','2021-07-01'),('001','2021-07-02'),('001','2021-07-04'),('001','2021-07-05'),('002','2021-07-08'),('002','2021-07-09'),('002','2021-07-10'),('002','2021-07-11'),('002','2021-07-15');

得到排序rank：

SET @rownumber=1;
SELECT login_id,login_date,
@rownumber:=CASE WHEN @current_name=login_id THEN @rownumber+1 ELSE 1 END AS rank,
@current_name:=login_id 
FROM 
(SELECT DISTINCT login_id,login_date FROM login_t ORDER BY login_id,login_date) t

日期和rank相减，相同则是连续登录

SET @rownumber=1;
SELECT 
t1.`login_id`,t1.`login_date`,rank,DATE_SUB(t1.login_date, INTERVAL t1.rank DAY)
FROM
(
SELECT login_id,login_date,
@rownumber:=CASE WHEN @current_name=login_id THEN @rownumber+1 ELSE 1 END AS rank,
@current_name:=login_id 
FROM 
(SELECT DISTINCT login_id,login_date FROM login_t ORDER BY login_id,login_date) t
) t1

最终sql：

SET @rownumber=1;
SELECT 
t1.`login_id`,t1.`login_date`,COUNT(1)
FROM
(
SELECT login_id,login_date,
@rownumber:=CASE WHEN @current_name=login_id THEN @rownumber+1 ELSE 1 END AS rank,
@current_name:=login_id 
FROM 
(SELECT DISTINCT login_id,login_date FROM login_t ORDER BY login_id,login_date) t
) t1

GROUP BY t1.`login_id`,DATE_SUB(t1.login_date, INTERVAL t1.rank DAY)
HAVING COUNT(1)>3;