连续登陆天数的SQL

DROP TABLE  if exists user_login;  
CREATE TABLE user_login(  
    user_id INT NOT NULL,  
    create_date DATETIME NOT NULL  
);
INSERT INTO user_login(user_id, create_date) VALUES(1,'2020-09-27 13:30:45');  
INSERT INTO user_login(user_id, create_date) VALUES(1,'2020-09-26 13:30:45');  

SELECT
	a.create_date, (@i := DATE_ADD(@i, INTERVAL - 1 DAY)) previous_day
FROM (
		SELECT create_date
		FROM user_login
		WHERE user_id = 2
		ORDER BY create_date DESC
	) a
INNER JOIN (
	SELECT @i := max(create_date) AS latest_date
	FROM user_login
	WHERE user_id = 2
	AND (
		TO_DAYS(create_date) = TO_DAYS(curdate())
		OR TO_DAYS(create_date) = TO_DAYS(
			DATE_ADD(curdate(), INTERVAL - 1 DAY)
		)
	)
) b ON b.latest_date IS NOT NULL
WHERE TO_DAYS(@i) = TO_DAYS(a.create_date);

今天与昨天是否登陆了，如果没有登陆就不用计算了，为0.
如果今天与昨天登陆了，就用最近登陆的变量逐减，查询出连续登陆的记录。

sql优先级：
from>on>join>where>group by>having>select>distinct>order by>top