Tips:混淆的知识点
在合并表格的过程中,存在几种合并方式,下面简单介绍一下这些方式(此处以两张表格的情况为例解释):
合并的过程中,第一张表格在最左边,之后的表格依次添加在右边
备注:只写一个join时默认为inner join模式
- inner join 最终结果为在两张表格中都匹配上的数据项
- left join 最终结果为inner join结果加上左侧表格(此处为第一张表)未匹配上的数据
- right join 最终结果为inner join结果加上右侧表格(此处为第一张表)未匹配上的数据
- full join 最终结果为inner join加上左侧和右侧两张表中未匹配上的数据
Q1:
select
university,
difficult_level,
round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from question_practice_detail as qpd
left join user_profile as up
on up.device_id = qpd.device_id
left join question_detail as qd
on qd.question_id = qpd.question_id
group by university,difficult_level
Q2:
什么时候使用left join?
什么时候使用inner join?
多表联查时如何界定每一项查询结果?
select
up.university,qd.difficult_level,
round((count(qpd.question_id)/count(distinct qpd.device_id)),4) as avg_answer_cnt
from question_practice_detail as qpd
inner join user_profile as up
on up.device_id = qpd.device_id and university = '山东大学'
inner join question_detail as qd
on qd.question_id = qpd.question_id
group by difficult_level