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

SELECT u.university, COUNT(q.question_id) / COUNT(DISTINCT u.device_id) AS avg_answer_cnt
FROM question_practice_detail AS q
LEFT OUTER JOIN user_profile AS u 
ON u.device_id = q.device_id 
GROUP BY u.university;

区分内联结（INNER JOIN）与左外联结（LEFT OUTER JOIN）、右外联结（RIGHT OUTER JOIN）的区别：INNER JOIN联结两个表时，只保留两个表存在相等关系的行；LEFT OUTER JOIN联结两个表时，保留LEFT OUTER JOIN左边表所有行，保留右面表的相等行；RIGHT OUTER JOIN保留RIGHT OUTER JOIN右边的表所有行，保留左面表的相等行。