select university,
difficult_level,
round((count(qpd.question_id)/count(distinct qpd.device_id)),4) as avg_answer_cnt
//注意:共同列必须指明来自那张表
from question_practice_detail qpd//qpd可以分别与up、qd进行连接
inner join user_profile up
on up.device_id = qpd.device_id
inner join question_detail qd
on qd.question_id = qpd.question_id
group by university,difficult_level;
知识点补充:mysql连接分类:内连接、外连接、全连接
内连接(inner join):等值连接、不等连接、自然连接
等值连接:公共列值相等
不等连接:公共列值比大小(>、<、<>())
自然连接:无需指定公共列,公共列只出现一次
外连接(outer join):左外连接,右外连接