题解 | #月均完成试卷数不小于3的用户爱作答的类别#

select tag, COUNT(*) AS tag_cnt
FROM exam_record AS er
JOIN examination_info AS ei using(exam_id)
#要对uid进行限制当月均完成试卷数不小于3的用户
WHERE uid in (
    select uid FROM exam_record 
    WHERE submit_time is NOT NULL #得是完成的
    group by uid
    HAVING COUNT(*) / COUNT(DISTINCT MONTH(submit_time)) >= 3
)        
GROUP BY tag
ORDER BY tag_cnt DESC

题目要求的是用户每个月平均回答次数不小于3的作答类型和及这些用户对应作答类型的回答次数。

既然要有tag 和次数，那最后肯定是按tag进行分组的，次数就是COUNT(*) ，然后再对不小于3的用户这一条件进行限制。

用子查询对uid限制，用户的回答次数，那么子查询里面肯定要对用户进行分组

where uid in(
  select uid FROM exam_record
  WHERE submit_time is NOT NULL #得是完成的
  group by uid
  #进行对次数的筛选
  HAVING COUNT(*) / COUNT(distinct MONTH(submit_time)) >= 3 #总次数除以一共有几个月有回答就是月平均次数
)