题解 | #统计每个学校的答过题的用户的平均答题数#

#方法1 cross join where
# select university,round(count(qpd.device_id)/count(distinct(qpd.device_id)),4) as avg_answer_cnt
# from user_profile as up
# cross join question_practice_detail as qpd
# where up.device_id=qpd.device_id
# group by up.university
# order by up.university asc

#方法2 inner join on
# select up.university,round(count(qpd.device_id)/count(distinct(qpd.device_id)),4) as avg_answer_cnt
# from user_profile as up
# inner join question_practice_detail as qpd
# on up.device_id=qpd.device_id
# group by up.university
# order by up.university asc

#方法3 right join，因为up表中可能有没有答题的，所以left join会有up表中多余id的空值，qpd表中肯定是答过题的，因此以qpd表为主用right join
select up.university,round(count(qpd.device_id)/count(distinct(qpd.device_id)),4) as avg_answer_cnt
from  user_profile as up
right join  question_practice_detail as qpd
on up.device_id=qpd.device_id
group by up.university
order by up.university asc