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

终于不用看题解完成一个难题，题目很绕。

需要解决的点：

1.用户

条件：月均，已完成试卷数>=3 来筛选出用户

select uid from  ( select uid,month(submit_time) from exam_record em
  group by uid,month(submit_time)
  having count(submit_time)>=3) ec

因为是月均，所以按照uid和month()分组

然后把这部分用户的所有信息挑出来作为新表与examination_info 表进行连接

select *  from exam_record ex 
 where uid in (select uid from  ( select uid,month(submit_time) from exam_record em
  group by uid,month(submit_time)
  having count(submit_time)>=3) ec)

这里用到的是in

2.爱作答的类别

两表连接，最终的代码

select tag,count(start_time ) as tag_cnt  from examination_info a
join (select *  from exam_record ex 
 where uid in (select uid from  ( select uid,month(submit_time) from exam_record em
  group by uid,month(submit_time)
  having count(submit_time)>=3) ec)) b
on a.exam_id =b.exam_id 
group by tag
order by tag_cnt desc