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

img


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

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

改成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;