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

select level, COUNT(distinct er.uid) AS level_cnt
FROM user_info AS ui 
JOIN exam_record AS er using(uid)
JOIN examination_info AS ei using(exam_id)
WHERE ei.tag = 'SQL' AND score > 80
GROUP BY level
ORDER BY level_cnt DESC, level DESC

子查询

select level, COUNT(uid) AS level_cnt
FROM user_info AS ui 
WHERE uid in (
    select uid FROM exam_record
    WHERE score > 80 AND exam_id in(
        select exam_id FROM examination_info
        WHERE tag = 'SQL'
    )
)
GROUP BY level
ORDER BY level_cnt DESC, level DESC

关于子查询和关联的用法

我的理解是如果存在一个表 包含了所需要的字段 就可以用子查询

否则用关联