【学习】10、综合练习：统计复旦用户8月练题情况

题目： 作为牛客网的数据分析师，现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0。

select u.device_id,u.university,
sum(q.question_id and month(q.date)=8) as question_cnt,
sum(if(q.result='right' and month(q.date)=8,1,0)) as right_question_cnt
from user_profile as u
left JOIN question_practice_detail as q

on u.device_id=q.device_id
where u.university='复旦大学'
group by u.device_id

这么写也对：

select u.device_id,u.university,
sum(q.question_id and month(q.date)=8) as question_cnt,
sum(if(q.result='right' and month(q.date)=8,1,0)) as right_question_cnt
from user_profile as u,question_practice_detail as q
where u.university='复旦大学' and u.device_id=q.device_id
group by u.device_id

或：

select u.device_id,u.university,
count(q.question_id) as question_cnt,
count(if(q.result='right',1,null)) as right_question_cnt
from user_profile u
INNER JOIN question_practice_detail  q

where u.device_id=q.device_id and u.university='复旦大学'
group by u.device_id