题解 | #统计每个用户的平均刷题数#

• 可以用左链接，以第二个表为基础，分别链接第一和第三个表
• 链接三个表后，以学校和难度分组
• 分组完成后用having 过滤

SELECT t1.university, 
t3.difficult_level, 
COUNT(t2.result) / COUNT(DISTINCT t2.device_id) AS avg_answer_cnt
FROM question_practice_detail AS t2
LEFT JOIN user_profile AS t1
ON t2.device_id = t1.device_id
LEFT JOIN question_detail AS t3
ON t2.question_id = t3.question_id
GROUP BY t1.university, t3.difficult_level
HAVING t1.university = '山东大学';