MySQL用户登录次数（日限4次）统计及时间范围查询需求

没问题，我来一步步帮你搞定这三个MySQL查询需求。首先先做个合理假设：你的登录记录表名为user_login_records，包含两个核心字段：user_id（用户唯一标识）和login_time（登录时间，datetime类型）——如果你的表名、字段名不一样，直接对应替换就行。

1. 统计每个用户的有效登录次数（单日超4次按4次计算） #

这一步需要先按用户+日期维度计算单日登录次数，再把超过4次的部分截断为4，最后按用户汇总总次数：

SELECT 
    user_id,
    SUM(daily_valid_count) AS total_valid_logins
FROM (
    SELECT 
        user_id,
        DATE(login_time) AS login_date,
        LEAST(COUNT(*), 4) AS daily_valid_count
    FROM user_login_records
    GROUP BY user_id, DATE(login_time)
) AS daily_user_logins
GROUP BY user_id;

逻辑解释： #

• 内层子查询daily_user_logins：把每条登录记录按用户和登录日期分组，用COUNT(*)统计单日登录次数，再用LEAST(COUNT(*), 4)把超过4的次数强制转为4，得到用户单日的有效登录数。
• 外层查询：按用户分组，把单日有效数求和，得到每个用户的总有效登录次数。

2. 计算所有用户的总有效登录数 #

直接基于上面的查询，对用户的总有效登录数求和即可：

SELECT SUM(total_valid_logins) AS overall_total_logins
FROM (
    SELECT 
        user_id,
        SUM(daily_valid_count) AS total_valid_logins
    FROM (
        SELECT 
            user_id,
            DATE(login_time) AS login_date,
            LEAST(COUNT(*), 4) AS daily_valid_count
        FROM user_login_records
        GROUP BY user_id, DATE(login_time)
    ) AS daily_user_logins
    GROUP BY user_id
) AS user_total_logins;

或者更简洁的写法（跳过用户维度，直接按日期计算每日总有效数再求和）：

SELECT SUM(daily_overall_valid) AS overall_total_logins
FROM (
    SELECT 
        DATE(login_time) AS login_date,
        SUM(LEAST(COUNT(*), 4)) AS daily_overall_valid
    FROM user_login_records
    GROUP BY DATE(login_time), user_id
) AS daily_overall;

两种写法结果一致，第二种少一层嵌套，效率可能更高。

3. 倒推找到累计达指定总登录数的最早日期 #

假设我们要找累计达100次的最早日期，这里需要先计算每日的总有效登录数，再从当前日期往前倒序累计，直到累计和达到目标值，取对应的最早日期：

WITH daily_valid AS (
    -- 计算每日的总有效登录数
    SELECT 
        DATE(login_time) AS login_date,
        SUM(LEAST(COUNT(*), 4)) AS daily_total
    FROM user_login_records
    GROUP BY DATE(login_time), user_id
    GROUP BY login_date  -- 对用户单日的有效数求和，得到当日总有效数
),
cumulative_valid AS (
    -- 从当前日期倒序，计算累计有效登录数
    SELECT 
        login_date,
        daily_total,
        SUM(daily_total) OVER (ORDER BY login_date DESC) AS cumulative_total
    FROM daily_valid
    WHERE login_date <= CURDATE()  -- 只统计当前及之前的日期
)
-- 找到累计和首次>=目标值的最早日期（因为是倒序，所以取最小的login_date）
SELECT MIN(login_date) AS earliest_date
FROM cumulative_valid
WHERE cumulative_total >= 100;  -- 这里替换成你需要的指定总登录数

逻辑解释： #

1. daily_valid CTE：先按用户+日期计算单日有效数，再按日期汇总得到当日所有用户的总有效登录数。
2. cumulative_valid CTE：用窗口函数SUM(...) OVER (ORDER BY login_date DESC)从最近的日期开始倒序累计每日有效数，得到每个日期对应的累计总和。
3. 最后筛选累计总和>=目标值的所有日期，取其中最小的（也就是最早的）日期，就是我们要找的结果。

如果你的MySQL版本低于8.0（不支持窗口函数），可以用变量来实现累计求和：

SELECT MIN(login_date) AS earliest_date
FROM (
    SELECT 
        login_date,
        @cumulative := @cumulative + daily_total AS cumulative_total
    FROM (
        SELECT 
            DATE(login_time) AS login_date,
            SUM(LEAST(COUNT(*), 4)) AS daily_total
        FROM user_login_records
        GROUP BY DATE(login_time), user_id
        GROUP BY login_date
        ORDER BY login_date DESC
    ) AS daily_valid
    CROSS JOIN (SELECT @cumulative := 0) AS init
) AS cumulative_valid
WHERE cumulative_total >= 100;

内容的提问来源于stack exchange，提问作者Mudders