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

select u.device_id,u.university,
count(q.question_id) as question_cnt,
count(case when q.result ='right' then 1 else null end) as right_question_cnt
from user_profile u
left join question_practice_detail q
on u.device_id = q.device_id
and substring(q.date,1,7)='2021-08'
where u.university ='复旦大学'
group by u.device_id

1. 在8月份没有练习过的用户，答题数结果返回0.---要用left join
2. group by要用u.device_id，而不是q.device_id
3. 要记住执行顺序From--on--join--where--group by--having--select--distinct--order by--limit