SELECT t1.author_id, t2.author_level, t1.days_cnt
FROM author_tb t2 JOIN
(
SELECT a.author_id, COUNT(*) days_cnt
FROM
(
SELECT author_id, answer_date,
DENSE_RANK()OVER(PARTITION BY author_id ORDER BY answer_date) rk
FROM answer_tb
GROUP BY author_id, answer_date #这一行起到了去重的作用,方便后面直接用count行数
) a
GROUP BY a.author_id, DATE_SUB(a.answer_date,INTERVAL a.rk DAY)
HAVING COUNT(*)>=3
#这一行就是直接count连续=>3天的那些日期;有可能234号连续;678号又连续一次;这种情况在最后的时候count(*)难道不是算6次?
) t1
ON t1.author_id = t2.author_id
ORDER BY t1.author_id
# 我其实对这个解法有点小疑问;如上备注