题解 | #查询连续登陆的用户#

一、核心思路

使用 ROW_NUMBER() + DATE_SUB 构造“连续组标识”（即“初始日期”），然后按组统计连续天数。

二、正确题解

SELECT DISTINCT user_id
FROM (
    SELECT 
        user_id,
        DATE(log_time) AS log_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY DATE(log_time)) AS rn,
        DATE_SUB(DATE(log_time), INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY DATE(log_time)) DAY) AS grp
    FROM login_tb
    WHERE user_id IN (SELECT user_id FROM register_tb)
) t1
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id;

使用 ROW_NUMBER + DATE_SUB 的连续分组法，可以轻松扩展到“连续≥N天”，能解决所有连续日期问题。