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

# 查询有某个月份完成试卷数不小于3的所有用户的id
select distinct uid
from(
    select uid, date_format(submit_time, '%Y%m') as fin_month
    from exam_record
    where submit_time is not null
    group by uid, date_format(submit_time, '%Y%m')
    having count(*)>=3
) k1
# 查询以上用户爱作答的试卷类别及作答次数
select tag, count(*) as tag_cnt
from exam_record er
join examination_info ei
on er.exam_id=ei.exam_id
where uid in (
    select distinct uid
    from(
        select uid, date_format(submit_time, '%Y%m') as fin_month
        from exam_record
        where submit_time is not null
        group by uid, date_format(submit_time, '%Y%m')
        having count(*)>=3
    ) k1
)
group by tag
order by tag_cnt desc;

需要注意的是：题意中的“当月均完成试卷数”不小于3的用户们是指，有某个月份完成试卷数不小于3的用户。故我们应该先找出满足这个条件的用户id，然后再查询这些用户作答的试卷类别及他们作答这些试卷的次数