如何在SQL中使用子查询快速定位连续签到天数最多的用户？-SQL-PHP中文网

MySQL 5.7及以下需用变量模拟LAG计算连续签到天数：先按user_id、sign_date排序，用变量记录前一行日期，连续则累加，否则重置为1；须显式ORDER BY、分离变量初始化、DATE类型校验，并通过两层子查询（内层算cnt、外层MAX）避免语法错误。

如何在sql中使用子查询快速定位连续签到天数最多的用户？

用子查询+变量计算连续签到天数

MySQL 5.7 或更早版本不支持 LAG() 和窗口函数，得靠用户变量模拟“上一行”逻辑。核心思路是：先按用户、日期排序，再用变量记录前一次的 user_id 和 sign_date，一旦发现当前行和上一行属于同一用户且日期连续（DATEDIFF(sign_date, @prev_date) = 1），就累加计数；否则重置为 1。

容易踩的坑：

必须确保 ORDER BY user_id, sign_date 在子查询中显式声明，否则变量行为不可控
变量初始化不能放在同一语句中（如 @cnt := 0 写在 SELECT 列表里），应前置 SET 或用 (SELECT ...) 包裹初始化
日期字段必须是 DATE 类型，若为 DATETIME 需先 DATE(sign_time) 转换，否则 DATEDIFF 可能误判

子查询嵌套结构怎么组织才不报错

外层查最大值，内层算每个用户的最长连续段——这种需求必须用两层子查询。第一层（最内）生成带 cnt 的中间结果；第二层按 user_id 分组取 MAX(cnt)；第三层（可选）再关联原表或用 LIMIT 1 拿最高者。常见错误是把变量逻辑直接塞进 GROUP BY 查询里，导致 MySQL 报错 Unknown column 'cnt' in 'field list' 或聚合顺序错乱。

实操建议：

把变量计算封装进一个派生表（即 (SELECT ...) AS t1），外层只做聚合
避免在子查询里同时引用未定义的别名（如 SELECT @cnt := IF(..., @cnt + 1, 1) AS cnt, cnt 是错的，cnt 不能在同级 SELECT 中复用）
测试时先跑内层子查询，确认 cnt 列输出符合预期再包外层

为什么用 NOT EXISTS 比 JOIN 更适合查“断点”

另一种思路是定位连续区间的起点：某天签到了，但前一天没签到。这时 NOT EXISTS 比 LEFT JOIN ... IS NULL 更清晰可靠，尤其当表有重复记录或索引缺失时。NOT EXISTS 能天然跳过重复，且优化器更容易走 sign_date 索引。

示例关键片段：

WHERE NOT EXISTS (
  SELECT 1 FROM sign_log s2 
  WHERE s2.user_id = s1.user_id 
    AND s2.sign_date = DATE_SUB(s1.sign_date, INTERVAL 1 DAY)
)

注意点：

s2.sign_date 必须和 s1.sign_date 类型一致，否则隐式转换会让索引失效
如果业务允许“同天多次签到”，需先去重（DISTINCT user_id, DATE(sign_time)），否则起点判断会出错
这个方法本身不直接给出天数，需配合自连接或窗口函数补全，不适合纯子查询单步求解

MySQL 8.0+ 直接用 ROW_NUMBER() 更稳

如果你用的是 MySQL 8.0 或更高版本，放弃变量方案，改用 ROW_NUMBER() + 差值分组（gaps-and-islands 经典解法）。原理是：对每个用户按日期排序后，用 sign_date - INTERVAL ROW_NUMBER() DAY 得到一个“岛标识”，相同标识的行就是连续段。

关键代码结构：

SELECT user_id, COUNT(*) AS days
FROM (
  SELECT user_id, sign_date,
         DATE_SUB(sign_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_date) DAY) AS grp
  FROM sign_log
) t
GROUP BY user_id, grp

优势明显：