# 先对用户登录信息表login_tb去重,尽管该题说忽略单日多次登录情况,但现实中有可能单日多次登录 with QC as (select user_id,date_format(log_time,'%Y-%m-%d') t from login_tb group by user_id,date_format(log_time,'%Y-%m-%d')), # 确保是新用户 New as (select * from QC where exists ( select 1 from register_tb r where QC.user_id=r.user_id)), # 辅助列 ikun as (select user_id,date_sub(t, interval row_number()over(partition by user_id order by t) day) start_day from New) select distinct user_id from ikun group by user_id,start_day having count(*)>=3