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

解题思路

1. 筛选出tag为"SQL"并且分数大于80的exam_id
2. 选出答过sql题目的uid，（用distinct）
3. 在用户表中用where uid in进行限制
4. 按level分组，level_cnt desc 排序

代码

select level, count(uid) level_cnt from user_info
where uid in (
select distinct er.uid from exam_record er
where er.exam_id in (select exam_id from examination_info where tag = "SQL") and er.score > 80 
)
group by level
order by level_cnt desc