题解 | #月均完成试卷数不小于3的用户爱作答的类别#
月均完成试卷数不小于3的用户爱作答的类别
http://www.nowcoder.com/practice/b1d13efcfa0c4ecea517afbdb9090845
感觉自己写的乱七八遭，运行效率还不错…… 另外，牛客网这题目就不能出的没有疑义吗？月均数>=3搞得我以为每个月都要>=3
-- 1，查询出uid,tag,month,完成情况
-- 2，找出月均完成大于等于3的用户
-- 3，统计这些用户的类别情况（包括没完成的）
with tmp as-- 统计每个用户每个月的完成数
(
select uid,mon,sum(compl)as total from
(select uid,tag,month(start_time)as mon,if(submit_time is null,0,1)as compl
from exam_record
left join examination_info
using(exam_id)
)c
group by uid,mon
)
select tag,count(tag)as tag_cnt from examination_info ei
join exam_record er
on er.uid in (select distinct uid from tmp where total>=3) and ei.exam_id=er.exam_id
group by tag
order by tag_cnt desc
