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

select tag,count(tag) as tag_cnt
from
(
select tag from
(select exam_id from
    (
        select * from exam_record er
        where er.uid in
        (
            # 查找月均完成试卷数不小于3的用户list
            select uid from
            (
            select 
                uid, 
                round(count(date_format(submit_time, '%Y%m')) / count(distinct date_format(submit_time, "%Y%m")), 2) as complete_avg
            from exam_record
            group by uid
            having complete_avg >= 3
            ) as over_three_1
        )
    ) as over_three_2
) as eti
left join examination_info ei
on eti.exam_id = ei.exam_id
) as tct
group by tag
order by tag_cnt desc;

【知识点】子查询的别名问题、date_format函数、GROUP BY、HAVING

date_format(submit_time, '%Y%m')——后面的格式需要加引号，不然会报错

SELECT xxx FROM (子查询) AS [别名]——子查询得到的表要加别名，不然会报错

WHERE xxx IN （子查询）——这里的我没加，通过了

查了一下子查询的别名问题，这个例题正好把两种情况都涉及到了：

【参考链接】

https://zhuanlan.zhihu.com/p/336820866