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

【场景】：列合并、select条件语句、join on 条件

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

分析思路

难点：

1.统计出“当月均完成试卷数”不小于3的用户们，with子查询和from子查询一定要去重,in子查询不用去重

2.多表做连接

3.join on 的连接条件中相当于where条件，是可以写每个表单独的条件

（1）找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬

​ [条件]：where tag = 'SQL' and difficulty = 'hard' and level = '7' group by uid having avg(score) >= 80

（2）统计用户2021年试卷总完成次数

​ [条件]：year(submit_time) = 2021

（3）统计用户2021年题目总练习次数

​ [条件]：year(submit_time) = 2021

（4）分别将符合条件的用户的试卷总完成数和题目总完成数两列合并，按试卷完成数升序，按题目练习数降序

​ [使用]：多表左连接使用 left join using( ); order by exam_cnt asc,question_cnt desc

最终结果

select 查询结果 [用户ID; 试卷总完成数; 题目总完成数]
from 从哪张表中查询数据[多个join连接的表]
where 查询条件 [高难度SQL试卷得分平均值大于80并且是7级的红名大佬]
order by 对查询结果排序 [按试卷完成数升序，按题目练习数降序];

扩展：

前往查看MySQL 合并查询join 查询出的不同列合并到一个表中

求解代码

方法一：

with 子查询

with
    temp as(
        #找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬
    	select 
            uid
        from exam_record
        join examination_info using(exam_id)
        join user_info using(uid)
        where tag = 'SQL'
        and difficulty = 'hard'
        and level = '7'
        group by uid having avg(score) >= 80
    ),
    main as(
        #统计2021年试卷总完成次数
    	select 
            uid,
            count(submit_time) as exam_cnt
        from exam_record
        where year(submit_time) = 2021
        group by uid
    ),
    attr as(
        #统计2021年题目总练习次数
    	select 
            uid,
            count(submit_time) as question_cnt
        from practice_record
        where year(submit_time) = 2021
        group by uid
    )
#分别将符合条件的用户的试卷总完成数和题目总完成数两列合并，按试卷完成数升序，按题目练习数降序
select 
    uid,
    exam_cnt,
    if(question_cnt is null,0,question_cnt)
from temp
left join main using(uid)
left join attr using(uid)
order by exam_cnt asc,question_cnt desc

方法二

from 子查询、in 子查询

# 分别找第二列和第三列
select 
    uid,
    exam_cnt,
    if(question_cnt is null,0,question_cnt)
from(
    #统计2021年试卷总完成次数
    select
        uid,
        count(submit_time) as exam_cnt
    from exam_record
    where year(submit_time) = 2021
    group by uid
     ) main
left join(
    #统计2021年题目总练习次数
    select 
        uid,
        count(submit_time) as question_cnt
    from practice_record
    where year(submit_time) = 2021
    group by uid
    ) attr using(uid)
where uid in(
    #找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬
    select 
        uid
    from exam_record
    join examination_info using(exam_id)
    join user_info using(uid)
    where tag = 'SQL'
    and difficulty = 'hard'
    and level = '7'
    group by uid having avg(score) >= 80
    )
order by exam_cnt asc,question_cnt desc

方法三

with 子查询、in 子查询

with
	main as(
        #统计2021年试卷总完成次数
    	select 
            uid,
            count(submit_time) as exam_cnt
        from exam_record
        where year(submit_time) = 2021
        group by uid
    ),
    attr as(
        #统计2021年题目总练习次数
    	select 
            uid,
            count(submit_time) as question_cnt
        from practice_record
        where year(submit_time) = 2021
        group by uid
    )

#分别将符合条件的用户的试卷总完成数和题目总完成数两列合并，按试卷完成数升序，按题目练习数降序
select
    uid,
    exam_cnt,
    if(question_cnt is null,0,question_cnt)
from main
left join attr using(uid)
where uid in(
    select
        uid
    from exam_record
    join examination_info using(exam_id)
    join user_info using(uid)
    where tag = 'SQL'
    and difficulty = 'hard'
    and level = '7'
    group by uid having avg(score) >= 80
)
order by exam_cnt asc,question_cnt desc

方法四

多表连接

select 
    uid,
    exam_cnt,
    if(question_cnt is null,0,question_cnt)
from exam_record
join examination_info using(exam_id)
join user_info using(uid)
left join(
    #统计2021年试卷总完成次数
    select
        uid,
        count(submit_time) as exam_cnt
    from exam_record
    where year(submit_time) = 2021
    group by uid
     ) main using(uid)
left join(
    #统计2021年题目总练习次数
    select 
        uid,
        count(submit_time) as question_cnt
    from practice_record
    where year(submit_time) = 2021
    group by uid
    ) attr using(uid)
#找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬
where tag = 'SQL'
and difficulty = 'hard'
and level = '7'
group by uid having avg(score) >= 80
order by exam_cnt asc,question_cnt desc

方法五

不使用列合并 使用 join on 条件

select
    a.uid,
    count(distinct a.exam_id) as exam_cnt,#统计2021年试卷总完成次数
    count(distinct b.id) as question_cnt#统计2021年题目总练习次数
from exam_record a
left join practice_record b
on a.uid = b.uid and year(a.submit_time) = 2021 and year(b.submit_time) = 2021
where a.uid in(
    #找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬
    select 
        a.uid
    from exam_record a
    join examination_info using(exam_id)
    join user_info using(uid)
    where tag = 'SQL'
    and difficulty = 'hard'
    and level = '7'
    group by a.uid having avg(score) >= 80) 
group by a.uid
order by exam_cnt,question_cnt desc