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

select up.device_id,up.university,count(qpd.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 up.device_id=qpd.device_id and month(qpd.date)=8
where university='复旦大学' 
group by device_id

注意

1. 左连接时候，不能写成select qpd.device_id，否则将device_id=4321的用户的device_id将被识别为None
2. on和where的区别，如果写成

select up.device_id,up.university,count(qpd.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 up.device_id=qpd.device_id 
where university='复旦大学' and month(qpd.date)=8
group by device_id

即将and放在where后面，将不能识别出4321那个用户。