任意两个连续自然月练题次数大于1的用户

Question

select t1.device_id
from
(select device_id,month(event_date) mon
from question_practice_detail
group by device_id,month(event_date)
having count(question_id)>1) t1
join 
(select device_id,month(event_date) mon
from question_practice_detail
group by device_id,month(event_date)
having count(question_id)>1) t2
on t1.device_id=t2.device_id
and t2.mon=date_sub(t1.mon,interval 1 month)=1

我不知道哪里错了！有人帮我看看嘛！

Answer 1

改成t2.mon = t1.mon+1;即可

select t1.device_id
from
(select device_id,month(event_date) mon
from question_practice_detail
group by device_id,month(event_date)
having count(question_id)>1) t1
join 
(select device_id,month(event_date) mon
from question_practice_detail
group by device_id,month(event_date)
having count(question_id)>1) t2
on t1.device_id=t2.device_id
and t2.mon=t1.mon+1;