题意:月均完成试卷数不小于3的用户们作答的试卷类别和作答次数
1、按用户分组(子查询)
统计用户有完成试卷的月份数
计算用户月均完成试卷数
2、按tag分组
输出试卷类别及作答次数
1、按用户分组(子查询)
统计用户有完成试卷的月份数
计算用户月均完成试卷数
2、按tag分组
输出试卷类别及作答次数
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 HAVING COUNT(exam_id) / COUNT(DISTINCT DATE_FORMAT(submit_time, '%Y%m')) >= 3 ) GROUP BY tag ORDER BY tag_cnt DESC