MySQL查询：统计指定用户最后连续有IN状态的天数

表结构与示例数据 #

需求说明 #

筛选user1时，统计该用户最后一段连续存在IN状态的天数，示例数据中结果为2。

SQL查询语句 #

SELECT COUNT(*) AS consecutive_days
FROM (
    SELECT 
        date_only,
        DATE_SUB(date_only, INTERVAL ROW_NUMBER() OVER(ORDER BY date_only) DAY) AS group_key
    FROM (
        -- 提取user1所有有IN状态的日期，去重
        SELECT DISTINCT DATE(dateTime) AS date_only
        FROM your_table_name
        WHERE userId = 'user1' AND status = 'IN'
    ) AS user_dates
) AS grouped_dates
GROUP BY group_key
ORDER BY MAX(date_only) DESC
LIMIT 1;

语句说明 #

1. 内层子查询user_dates：提取user1所有存在IN状态的日期，通过DISTINCT确保同一天只算一次。
2. 中间层grouped_dates：利用ROW_NUMBER()生成按日期排序的行号，用日期减去行号得到分组键——连续的日期会生成相同的group_key，以此区分不同的连续日期段。
3. 外层查询：按group_key分组后，取最大日期最新的分组，统计该分组的记录数即为最后一段连续天数。

注意：如果status字段实际存储值不带单引号，需将status = 'IN'调整为status = IN（或status = "IN"），匹配实际数据格式。

内容的提问来源于stack exchange，提问作者Ivan Faber Cavallo