题解 | #作答试卷得分大于过80的人的用户等级分布#

#方法一: 嵌套子查询
select
    level,
    count(level) level_cnt
from (
    select
    t1.exam_id,
    level,
    tag
from (
    select
        exam_id,
        level
    from exam_record er
    left join user_info ui on er.uid= ui.uid
    where er.submit_time is not null
    and er.score > 80
)t1
left join examination_info ei on t1.exam_id = ei.exam_id
where tag = 'SQL'
)t2
group by level
order by level_cnt desc;

#方法二:
select
    ui.level,
    count(ui.uid) level_cnt
from user_info ui where uid in (
    select
        er.uid
    from examination_info ei 
    join exam_record er using(exam_id) 
    where ei.tag = 'SQL' and er.submit_time is not null  and er.score > 80
)group by level
order by level_cnt desc;