不用窗口函数 求出连续出现连续登录问题

参考：https://www.5axxw.com/questions/content/q3dy0n

-- 建表导入数据
create table logs (Id int, Num int);
INSERT INTO `logs` VALUES (1,1),(2,1),(3,1),(4,2),(5,1),(6,2),(7,2),(8,3),(9,4),(10,1),(11,1),(12,1);

求连续三次出现的数字

select distinct l1.num as ConsecutiveNums from logs l1
inner join logs l2 on l1.id = l2.id - 1
inner join logs l3 on l1.id = l3.id + 1
where l1.num = l2.num and l2.num = l3.num

求连续两次出现的数字

select distinct l1.num as ConsecutiveNums from logs l1
inner join logs l2 on l1.id = l2.id - 1
where l1.num = l2.num 

同理：求出下面连续两天登录的用户

uid01,2018-02-28
uid01,2018-03-01
uid01,2018-03-02
uid01,2018-03-04
uid01,2018-03-05
uid01,2018-03-06
uid01,2018-03-07
uid02,2018-03-01
uid02,2018-03-02
uid02,2018-03-03
uid02,2018-03-06
uid03,2018-03-02
uid03,2018-03-03
uid03,2018-03-05
uid04,2018-02-28
uid04,2018-03-03
uid04,2018-03-04
uid04,2018-03-05
uid05,2018-03-01
uid05,2018-03-05

可以看出来，
连续两天登录的用户有uid01，uid02，uid03，uid04
连续三天登录的用户有uid01，uid02，uid04

建表导入数据
create table t_user (userId varchar(20),loginDate date);
insert into t_user values
('uid01','2018-02-28'),
('uid01','2018-03-01'),
('uid01','2018-03-02'),
('uid01','2018-03-04'),
('uid01','2018-03-05'),
('uid01','2018-03-06'),
('uid01','2018-03-07'),
('uid02','2018-03-01'),
('uid02','2018-03-02'),
('uid02','2018-03-03'),
('uid02','2018-03-06'),
('uid03','2018-03-02'),
('uid03','2018-03-03'),
('uid03','2018-03-05'),
('uid04','2018-02-28'),
('uid04','2018-03-03'),
('uid04','2018-03-04'),
('uid04','2018-03-05'),
('uid05','2018-03-01'),
('uid05','2018-03-05');

求连续两天登录的用户

select distinct(t1.userId) 
from t_user t1,t_user t2
where t1.userId=t2.userId 
and datediff(t1.loginDate,t2.loginDate)=1;

求连续三天登录的用户

select distinct(t1.userId)
from t_user t1
inner join t_user t2 on datediff(t1.loginDate,t2.loginDate)= -1
inner join t_user t3 on datediff(t1.loginDate,t3.loginDate)= 1
where t1.userId = t2.userId and t2.userId = t3.userId