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

SELECT tag, COUNT(tag) tag_cnt
FROM exam_record,examination_info
WHERE exam_record.exam_id=examination_info.exam_id AND
uid IN
(SELECT uid FROM exam_record
 GROUP BY uid
 HAVING SUM(IF(submit_time IS NOT NULL,1,0))/ COUNT(DISTINCT DATE_FORMAT(submit_time,'%Y%m'))>=3)
GROUP BY tag
ORDER BY tag_cnt DESC;

先找到符合条件的uid，用此做查询约束。 mark一下:第一，用"uid IN"而不是"uid ="；第二，HAVING后面可以跟这种略复杂的约束（带有集函数运算的）没关系，having短语作用于分组后的组，从中选择满足条件的组。