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

#首先检索某月作答个数大于等于3的用户，然后对于这些用户统计他们所有的试卷数据

select  tag,COUNT(tag) as tag_cnt from exam_record join examination_info using(exam_id) where uid in (select uid from exam_record where submit_time is not null group by uid,date_format(submit_time,'%y%m') having count(submit_time)>=3)

group by tag

order by tag_cnt desc

#首先要检索的数据是试卷类别和每个类别的试卷作答个数，所以首先根据类别分组，然后每个类别试卷的作答个数就是表连接起来之后的每个试卷的tag个数，要满足一个条件就是用户在某个月作答个数大于等于3，对于该条件做法是：先根据用户后根据年月分组，某个组（某个用户的某个年月）它的作答个数大于等于3即可，当然完成试卷肯定是先要求提交时间不为空