题解 | #满足条件的用户的试卷完成数和题目练习数#

满足条件的用户的试卷完成数和题目练习数

http://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf

要求：请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

思路如下：要求先让你找到满足条件的红名大佬。然后看结果示例是两列，而两列分别来源于两个表，一般我遇到这种情况会分开构建每一列然后用两表连接的方式得到最终结果。那么我们接下来只需要一步一步操作即可。

查询高难度SQL试卷得分平均值大于80并且是7级的红名大佬 uid

select er.uid
from exam_record er
join examination_info ei on er.exam_id =ei.exam_id 
join user_info ui on er.uid=ui.uid
where tag='SQL' 
and difficulty= 'hard'
and level >= 7  #感觉大于七级也应该是红名 所以加了大于号
group by uid 
having avg(score)>80

查询红名大佬 2021年试卷总完成次数

select uid , count(submit_time) exam_cnt
from exam_record
where year(submit_time) = 2021
and submit_time is not null
and uid in(
    select er.uid
    from exam_record er
    join examination_info ei on er.exam_id =ei.exam_id 
    join user_info ui on er.uid=ui.uid
    where tag='SQL' 
    and difficulty= 'hard'
    and level >= 7
    group by uid 
    having avg(score)>80
)
group by uid

查询红名大佬 2021年题目总练习次数

select uid, count(submit_time) question_cnt
from practice_record 
where year(submit_time) = 2021
and uid in(
    select er.uid
    from exam_record er
    join examination_info ei on er.exam_id =ei.exam_id 
    join user_info ui on er.uid=ui.uid
    where tag='SQL' 
    and difficulty= 'hard'
    and level >= 7
    group by uid 
    having avg(score)>80
)
group by uid

连接表2 表3，用ifnull 添加0 ，得到最终结果

select e.uid ,exam_cnt , ifnull(question_cnt,0) question_cnt
from (
    select uid , count(submit_time) exam_cnt
    from exam_record
    where year(submit_time) = 2021
    and submit_time is not null
    and uid in(
        select er.uid
        from exam_record er
        join examination_info ei on er.exam_id =ei.exam_id 
        join user_info ui on er.uid=ui.uid
        where tag='SQL' 
        and difficulty= 'hard'
        and level >= 7
        group by uid 
        having avg(score)>80
    )
    group by uid
) e
left join (
    select uid, count(submit_time) question_cnt
    from practice_record 
    where year(submit_time) = 2021
    and uid in(
        select er.uid
        from exam_record er
        join examination_info ei on er.exam_id =ei.exam_id 
        join user_info ui on er.uid=ui.uid
        where tag='SQL' 
        and difficulty= 'hard'
        and level >= 7
        group by uid 
        having avg(score)>80
    )
    group by uid
) q
on e.uid =q.uid
order by exam_cnt asc , question_cnt desc