# 方法1：用join...on显性连接--即外连接，链接后会保留空值的数据
# 优点：外关联是根据左/右表的数据量一条条进行关联，最后关联出来的数据量=左/右表数据量，则判断关联运行无误
select university,difficult_level,count(b.question_id)/count(distinct(b.device_id))
from
user_profile a
left join
question_practice_detail b
on a.device_id=b.device_id
right join
question_detail c #c表作为主表，按照难度进行拼接，否则会出现空值的情况
on b.question_id=c.question_id
group by university,difficult_level
# # 方法2： 用where隐性连接--即内连接，链接后会过滤掉空值的数据
# select university,difficult_level,count(b.question_id)/count(distinct(b.device_id))
# from
# user_profile a,
# question_practice_detail b,
# question_detail c
# where
# a.device_id=b.device_id and b.question_id=c.question_id
# group by university,difficult_level
