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

【分类】：嵌套子查询、多表连接

分析思路

难点： 1.计算不同试卷类型的用户人数要记得去重 2.如何实现 in 嵌套子查询

1.统计作答SQL类别的试卷

• [条件]：where tag = 'SQL' score > 80 2.统计作答SQL类别的试卷得分大于过80的人
• [条件]：where score > 80 and exam_id in 。。。 3.统计作答SQL类别的试卷得分大于过80的人的用户等级分布,按数量降序排序
• [使用]：group by level；order by level_cnt desc

扩展

前往查看：MySQL 嵌套子查询 with子句 from子查询 in子查询 join子查询

求解代码

方法一： in 子查询

select
    level,
    count(level) as level_cnt
from user_info
where uid in(
    select distinct
        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;

方法二：

多表连接

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