写点不一样的解法
uid,
exam_complete_cnt
from
(
select
r.uid, sum(submit_time is not null) exam_complete_cnt
from
exam_record r
where
(uid, date_format(start_time, '%Y-%m')) in (
select
uid, `month`
from
(
select
uid, `month`, `rank`
from
(
select
uid, date_format(start_time, '%Y-%m') `month`, dense_rank() over (partition by uid
order by
date_format(start_time, '%Y-%m') desc) `rank`
from
exam_record)s
where
`rank` <= 3)m)
group by
r.uid)t
where
t.uid not in (
select
r.uid
from
exam_record r
where
r.submit_time is null
and (uid, date_format(start_time, '%Y-%m')) in (
select
uid, `month`
from
(
select
uid, `month`, `rank`
from
(
select
uid, date_format(start_time, '%Y-%m') `month`, dense_rank() over (partition by uid
order by
date_format(start_time, '%Y-%m') desc) `rank`
from
exam_record)s
where
`rank` <= 3)m))
order by
exam_complete_cnt desc,
uid desc;