编辑代码

CREATE DATABASE 连续登录;
use 连续登录;

CREATE TABLE register_tb (
user_id int,
reg_time datetime,
reg_port varchar(255));


INSERT INTO register_tb VALUES(1101,'2022-02-08 07:23:15','pc');
INSERT INTO register_tb VALUES(1102,'2022-02-08 09:12:22','app');
INSERT INTO register_tb VALUES(1103,'2022-02-08 09:35:45','m');
INSERT INTO register_tb VALUES(1104,'2022-02-08 09:41:01','app');
INSERT INTO register_tb VALUES(1105,'2022-02-08 12:01:01','app');
INSERT INTO register_tb VALUES(1106,'2022-02-08 17:22:13','app');
INSERT INTO register_tb VALUES(1107,'2022-02-08 18:26:21','pc');
INSERT INTO register_tb VALUES(1108,'2022-02-08 19:16:21','pc');
INSERT INTO register_tb VALUES(1109,'2022-02-08 19:56:21','pc');

SELECT * FROM register_tb;

CREATE table login_tb(
log_id int,
user_id int,
log_time datetime,
log_port varchar(255)
);


INSERT INTO login_tb VALUES(101,1101,'2022-02-09 07:24:15','pc');
INSERT INTO login_tb VALUES(102,1102,'2022-02-09 09:12:57','app');
INSERT INTO login_tb VALUES(103,1003,'2022-02-09 09:36:11','m');
INSERT INTO login_tb VALUES(104,1102,'2022-02-10 09:37:01','app');
INSERT INTO login_tb VALUES(105,1104,'2022-02-10 12:01:46','app');
INSERT INTO login_tb VALUES(106,1106,'2022-02-10 10:23:01','app');
INSERT INTO login_tb VALUES(107,1003,'2022-02-10 10:43:01','m');
INSERT INTO login_tb VALUES(108,1102,'2022-02-11 11:56:47','app');
INSERT INTO login_tb VALUES(109,1104,'2022-02-11 14:52:37','app');
INSERT INTO login_tb VALUES(1010,1106,'2022-02-11 16:56:27','app');
INSERT INTO login_tb VALUES(1011,1003,'2022-02-11 17:43:01','m');
INSERT INTO login_tb VALUES(1012,1106,'2022-02-12 10:56:17','app');


select user_id
from (
select user_id,
date_sub(log_time,interval rk day) as diff
from (
select user_id,
date(log_time) as log_time,
row_number()over(partition by user_id order by date(log_time)) as rk
from login_tb
where user_id in (select user_id from register_tb)
) t1
) t2
group by user_id,diff
having count(diff)>=3
order by user_id