使用前
SELECT tag,COUNT(uid) as tag_rank FROM exam_record left join examination_info USING(exam_id)
WHERE
# month(start_time)=9 and
uid in(
select uid from exam_record WHERE month(start_time)=9 GROUP BY uid having count(submit_time)>=3
)
GROUP BY tag
# 答题次数降序
ORDER BY tag_rank desc
使用 子查询结构更加清晰
# 子查询
with t1 as (select uid from exam_record WHERE month(start_time)=9 GROUP BY uid having count(submit_time)>=3)
SELECT tag,COUNT(uid) as tag_rank FROM exam_record left join examination_info USING(exam_id)
WHERE
# month(start_time)=9 and
uid in(
select uid from t1
)
GROUP BY tag
# 答题次数降序
ORDER BY tag_rank desc