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

select tag, count(tag) tag_cnt
from exam_record
join examination_info
using(exam_id)
where uid in (
    select uid
    from exam_record
    inner join examination_info 
    using(exam_id)
    where month(submit_time) - month(start_time)=0
    group by uid
    having count(submit_time)/count(distinct date_format(submit_time, '%Y%m')) >=3
)
group by tag
order by tag_cnt desc

知识点：表子查询

最开始我在where语句里面写子查询

select tag, count(tag) tag_cnt
from (
	select *
	from exam_record
	……
)
join ……

问题在于，通过from里面的子查询，找出某月作答次数超过三次的用户的记录，

from 里面的子查询需要用到聚合 group by uid, 这样查询后返回的是聚合后的表，而不是单独的考试记录。

而在where 里面使用In关键字能找到满足条件的uid, 并在原来的表中筛选。

易错点：限制条件：某月答题数量大于三天的用户

题目买描述俄为

| 他们会在某一个月内（试卷的开始和结束时间都在同一个月）做三张及以上（≥3）试

代码里表现为月平均数量大于3次，其实不准确，

反例：用户1001在9月答题1次，在10月答题6次，这样统计下来也满足条件。

    select uid
    from exam_record
    inner join examination_info 
    using(exam_id)
    where month(submit_time) - month(start_time)=0
    group by uid, month(submit_time)
    having count(submit_time)>=3