题解 | #统计每个学校各难度的用户平均刷题数#

我看很多是查询多个表，然后根据id判断，最开始也是这么想的，不过鉴于先前一题是连接就用连接写了，感觉有些麻烦，也没有探究效率问题

# select university, qd.difficult_level,
# count(answer_cnt)/count(distinct up.device_id) avg_answer_cnt
# from user_profile up 
# join question_practice_detail qpd
# on qpd.device_id = up.device_id
# join question_detail qd
# on qd.question_id = qpd.question_id
# group by
# university, qd.difficult_level

select university , difficult_level , 
count(qpd.question_id)/count(distinct qpd.device_id) avg_answer_cnt
from user_profile up , question_practice_detail qpd , question_detail qd
where up.device_id = qpd.device_id and qpd.question_id = qd.question_id
group by university , difficult_level;