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

方法一：三表join

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

方法二：直接select...where 三表join效率不高，根据所需要的数据，查看三个表，寻找其中的联系，直接通过where筛选出来

select u.university, qd.difficult_level, count(qpd.question_id) / count(distinct(qpd.device_id)) as avg_answer_cnt
FROM
    user_profile as u,
    question_practice_detail as qpd,
    question_detail as qd
WHERE
    qpd.device_id = u.device_id and
    qpd.question_id = qd.question_id
group by u.university, qd.difficult_level