题解 | #任意两个连续自然月练题次数大于1的用户#
任意两个连续自然月练题次数大于1的用户
https://www.nowcoder.com/practice/a4cea6942a4f4354b0a0181aa5f446d2
这个方法好像很白痴,不过这已经是我尽自己所能想出来的办法了
select distinct a.device_id from
(select device_id,date_format(event_date,'%Y%m') dt from question_practice_detail
group by device_id,dt
having count(device_id) > 1) a
left join
(select device_id,date_format(event_date,'%Y%m') dt from question_practice_detail
group by device_id,dt
having count(device_id) > 1) b
on (a.device_id = b.device_id and substring(a.dt,1,4) = substring(b.dt,1,4) and substring(b.dt,5,2) = substring(a.dt,5,2) + 1) or (a.device_id = b.device_id and substring(b.dt,1,4) = substring(a.dt,1,4) + 1 and substring(a.dt,5,2) = '12' and substring(b.dt,5,2) = '01')
where b.device_id is not null
order by device_id desc