关于腾讯两道sql问题的解法

题目详见：https://zhuanlan.zhihu.com/p/117498021?utm_source=wechat_session&utm_medium=social&utm_oi=576132412245938176

模拟的实际数据：

第一题的解法：

SELECT user_id,date,attend,IF(@temp_id = user_id AND DATEDIFF(date,@temp_date) = 1 AND attend = 1,@num := @num + 1,IF(attend = 0,@num := 0,@num)) AS days,@temp_id := user_id,@temp_date := date FROM (SELECT * FROM visit ORDER BY user_id,date) AS t,(SELECT @temp_id := NULL,@temp_date := NULL,@num := 1) AS q

结果为：

第二题的解法：（在第一题解法得到的表的基础上使用group by排序即可）

SELECT user_id,max(days) AS max_attend_days FROM (SELECT user_id,date,attend,IF(@temp_id = user_id AND DATEDIFF(date,@temp_date) = 1 AND attend = 1,@num := @num + 1,IF(attend = 0,@num := 0,@num)) AS days,@temp_id := user_id,@temp_date := date FROM (SELECT * FROM visit ORDER BY user_id,date) AS t,(SELECT @temp_id := NULL,@temp_date := NULL,@num := 1) AS q) AS temp_table GROUP BY user_id;

结果为：

个人觉得解法比较正确，求指教。