問(wèn)題分解：

• 限定條件：需要是復(fù)旦大學(xué)的（來(lái)自表user_profile.university），8月份練習(xí)情況（來(lái)自表question_practice_detail.date）
• 從date中取month：用month函數(shù)即可；
• 總題目：count(question_id)
• 正確的題目數(shù)：sum(if(qpd.result='right', 1, 0))
• 按列聚合：需要輸出每個(gè)用戶的統(tǒng)計(jì)結(jié)果，因此加上group by up.device_id, up.university
• 篩選：使用having函數(shù)篩選， 加上having up.university

完整代碼：

select up.device_id, university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up

left join question_practice_detail as qpd
  on qpd.device_id = up.device_id and month(qpd.date) = 8
group by up.device_id,up.university
having up.university='復(fù)旦大學(xué)'