题解 | #统计复旦用户8月练题情况#

select
    user_profile.device_id,
    university,
    count(question_practice_detail.question_id),
    sum(
        if (question_practice_detail.result = 'right', 1, 0)
    )
from
    user_profile
    left join question_practice_detail on user_profile.device_id = question_practice_detail.device_id
    and month (question_practice_detail.date) = 8
where
    university = '复旦大学'
group by
    user_profile.device_id

首先合并两个表格，合并条件为user_profile.device_id = question_practice_detail.device_id和month (question_practice_detail.date) = 8筛选出8月份所有的答题人

选择学校为复旦大学的学校

通过device_id进行分组