题解 | #月均完成试卷数不小于3的用户爱作答的类别#
月均完成试卷数不小于3的用户爱作答的类别
http://www.nowcoder.com/practice/b1d13efcfa0c4ecea517afbdb9090845
明确题意：
统计出 月均完成试卷数不小于3的用户爱作答的类别及作答次数，按次数降序输出
问题拆解：
- 本题主要是考察知识点：group by、date_format，重点是逻辑理解！！
- DATE_FORMAT(submit_time, "%Y%m" ) 返回的是202109这样；
- 先按照group by uid,DATE_FORMAT(submit_time,'%Y-%m')分组，过滤出次数>=3的uid,得到t0
- 注意，此时得出的uid比如1002，在8和9月均>=3，所以需要对uid去重，得到t2
- t1关联t2，得到exam_record明细，后面便于计算tag_cnt
-
t1关联t3，得到tag
- 最后group by t3.tag分组得到次数
代码实现：
select t3.tag, count(t1.uid) as tag_cnt from exam_record t1 join ( select distinct uid from ( select uid,DATE_FORMAT(submit_time,'%Y-%m') as month , count(*) as c1 from exam_record where score is not null group by uid,DATE_FORMAT(submit_time,'%Y-%m') having count(*) >= 3 )t0 )t2 on t1.uid = t2.uid join examination_info t3 on t1.exam_id = t3.exam_id group by t3.tag order by tag_cnt desc ;
虾皮信息一面373人在聊