题解 | 手把手教程#统计每个用户的平均刷题数#

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
LEFT JOIN user_profile AS up
ON qpd.device_id = up.device_id
LEFT JOIN question_detail AS qd
ON qpd.question_id = qd.question_id
WHERE up.university = "山东大学"
GROUP BY qd.difficult_level;

好，sql join知识点确实是开窍了，写个我自己的开窍版解题过程

搭积木写法

第一步：先给表起别名

question_practice_detail AS qpd

user_profile AS up

question_detail AS qd

第二步：写where和group by

根据题的难度分组：GROUP BY qd.difficult_level

只选山东大学：up.university = "山东大学"

第三步：根据题的表，开始写第一行的SELECT

SELECT up.university, qd.difficult_level,

avg_answer_cnt没有出现在字段里，一看就是个计算结果，先把AS写上

AS avg_answer_cnt

那这个avg_answer_cnt要怎么算呢，每个难度的题目数量除以答题人数，就能算出来了

现在已经根据难度分组完了（GROUP BY qd.difficult_level），直接题目数量COUNT(qpd.question_id)除以答题人数COUNT(distinct qpd.device_id)就行了。答题人数的逻辑，就是看看有多少台设备，有多少台设备就假定有多少个人，所以是去重的设备数量 = 人数

COUNT(qpd.question_id)/COUNT(distinct qpd.device_id)

哦，题目还要求小数点保留四位：

ROUND(COUNT(qpd.question_id)/COUNT(distinct qpd.device_id), 4)

后面再加个别名：

ROUND(COUNT(qpd.question_id)/COUNT(distinct qpd.device_id), 4) AS avg_answer_cnt

别忘了写个FROM，这里FROM就看哪个表里的数据在搞计算

FROM question_practice_detail AS qpd

第四步，Join叠叠乐

找不同表的相同字段，把俩表叠一块：

LEFT JOIN user_profile AS up

ON qpd.device_id = up.device_id

LEFT JOIN question_detail AS qd

ON qpd.question_id = qd.question_id

最后，按照顺序，把所有的积木整理一下顺序：

SELECT FROM

LEFT JOIN ON

WHERE

GROUP BY

就可以得出答案了