本题知识点包括多表连接、分组聚合、子查询,代码及注释如下:
select tag,count(start_time) tag_cnt from exam_record t1 left join examination_info t2 on t1.exam_id = t2.exam_id // ③查询所需字段,需要注意,这里只需计算作答次数而非完成次数
where uid in (select uid from exam_record
group by uid,left(start_time,7) * 此处left函数可使用date_format(start_time,"%Y%m")代替 having count(if(submit_time is null,null,1)) >= 3) // ①分组聚合,求得当月完成数不小于的用户
group by tag // ②在筛选出得用户数据中进一步使用tag分组,计算不同类型题目的作答量
order by tag_cnt desc // ④按照作答量降序排列

京公网安备 11010502036488号