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

嵌套子查询

非常简洁

1. 找出SQL对应exam_id
2. 找出分数符合条件的uid
3. 统计uid

分别在三个表当中实现

select level, count(uid) as level_cnt
from user_info
where uid in (
    select distinct uid 
    from exam_record
    where exam_id in (
        select exam_id
        from examination_info
        where tag = 'SQL'
    ) and score > 80 and submit_time is not null

)
group by level
order by level_cnt desc

这个是一开始的想法，将子查询混合了联表，但是觉得这个不如上面的解法好

有两点需要注意

• 对uid进行去重
• submit_time 不能为空

select level, count(uid) as level_cnt
from user_info
where uid in (
    select uid
    from exam_record
    inner join examination_info using(exam_id)
    inner join user_info using(uid)
    where tag = 'SQL' and score > 80 and submit_time is not null
)
group by level
order by level_cnt desc