牛客SQL练习详解 04：多表查询

叮嘟！这里是小啊呜的学习课程资料整理。好记性不如烂笔头，今天也是努力进步的一天。一起加油进阶吧！

1、子查询

sql21 浙江大学用户题目回答情况

题目：
现在运营想要查看所有来自浙江大学的用户题目回答明细情况，
请你取出相应数据

法1： 子查询【in】
select device_id, question_id, result 
from question_practice_detail 
where device_id in 
(select device_id from user_profile where university = '浙江大学')

法2: 内连接查询
select q.device_id, q.question_id, q.result
from question_practice_detail q
join user_profile u
on q.device_id = u.device_id
where u.university='浙江大学'

2、链接查询

sql22 统计每个学校的答过题的用户的平均答题数

运营想要了解每个学校答过题的用户平均答题数量情况，
请你取出数据。

select university,
round(count(q.result)/count(distinct(q.device_id)),4) as avg_answer_cnt
from question_practice_detail q
left join user_profile u
on q.device_id = u.device_id.  
# on q.university=u.university
group by university

平均回答数=回答数/回答的人；即 result/device_id  
关键词 DISTINCT 用于返回唯一不同的值

sql23 统计每个学校各难度的用户平均刷题数

题目：
运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，
请你写SQL取出相应数据

解题思路：
1，明确需要按照学校和难度进行分组；
2，用户平均答题量 = 答题总数 / 用户数，要明确答题总数需要用question_id的行数来表示，
  也就是需对question_id列进行计数，用户数是device_id的行数，其中答题的题目可以重复，用户则不能重复计数
3，注意三个表的联结问题，在两个表的基础上直接增加即可

法1:
select 
university, 
difficult_level, 
round(count(qpd.result)/count(distinct(qpd.device_id)),4) as avg_answer_cnt
from question_practice_detail qpd
left join user_profile u
on qpd.device_id=u.device_id
right join question_detail qd
on qpd.question_id=qd.question_id
# group by university  【注意错误】
group by university, qd.difficult_level

法2:
select 
university, 
difficult_level, 
round(count(qpd.result)/count(distinct(qpd.device_id)),4) as avg_answer_cnt
from 
question_practice_detail qpd, #注意不要缺少逗号
user_profile u,
question_detail qd
where qpd.device_id=u.device_id and qpd.question_id=qd.question_id
group by university, difficult_level

sql24 统计每个用户的平均刷题数

题目：
运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，
请取出相应数据

select 
u.university, 
qd.difficult_level,
round(count(qpd.result)/count(distinct(qpd.device_id)),4)
as avg_answer_cnt
from
user_profile u,
question_practice_detail qpd,
question_detail qd
where u.device_id=qpd.device_id 
and qpd.question_id=qd.question_id

and university='山东大学'
#and u.university='山东大学'

group by u.university, qd.difficult_level
# having u.university='山东大学'

3、组合查询

sql25 查找山东大学或者性别为男生的信息

题目：
现在运营想要分别查看学校为山东大学或者性别为男性的用户的
device_id、gender、age和gpa数据，
请取出相应结果，结果不去重。

select device_id, gender, age, gpa
from user_profile
# where university='山东大学' or gender='male'  【错误答案】
where university='山东大学' 

union all 

select device_id, gender, age, gpa
from user_profile
where gender='male'

【考察or与union的细节使用。】
Union all：
对两个结果集进行并集操作，包括重复行，不进行排序；

其实这里的不去重表示只要满足一个条件就被筛选出来，但总会存在一个人满足了两个条件只筛选一次。
这里的坑时使用or，因为or自带去重。
union等价于or，但union all 可以不去重。

Ending！
更多课程知识学习记录随后再来吧！

就酱，嘎啦！

注：
人生在勤，不索何获。