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

# select u.device_id,u.university,sum(
#     case when result is not null then 1
#          else 0
#     end    
# ) as question_cnt,sum(
#     case when result='right' then 1
#          else 0
#     end
# ) as right_question_cnt
# from (
#     select *
#     from user_profile
#     where university='复旦大学'
# ) as u 
# left join(
#     select *
#     from question_practice_detail
#     where month(date)=8
# ) as qp
# on u.device_id=qp.device_id
# group by u.device_id



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

count(列名) ：不统计None 值的计数

if(条件判断，条件成立时的取值，条件不成立时的取值) ：单case条件判断的简单写法