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

SELECT 
    university,
    difficult_level,
    round(count(t1.question_id) / count(distinct t1.device_id),4) as avg_answer_cnt


FROM question_practice_detail t1 

    left join user_profile t2
    on t1.device_id = t2.device_id

    left join question_detail t3
    on t1.question_id = t3.question_id

GROUP BY university,difficult_level

三表连接的两种方式：

• FROM 表1 left join 表2 on 表1.a = 表2.b
• left join 表3 on 表1.c = 表3.d
• from 表1,(select...from...where...) as 表2, (select...from...where...) as 表3
• where 表1.a=表2.b and 表2.b=表3.c