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

# select device_id, university, count(question_id) question_cnt, right_question_cnt
# from user_profile a 
# left join question_practice_detail b
# on a.device_id = b.device_id
# where university = '复旦大学'
# group by a.device_id

select a.device_id, university, 
case
    when b.question_cnt is null then 0
    else b.question_cnt
end as question_cnt, 
case
    when right_question_cnt is null then 0
    else right_question_cnt
end as right_question_cnt
from user_profile a
left join
(select device_id, count(question_id) question_cnt
from question_practice_detail
where month(date) = 8
group by device_id) as b
on a.device_id = b.device_id
left join
(select device_id, count(result) right_question_cnt
from question_practice_detail
where result = 'right' and month(date) = 8
group by device_id) as c
on a.device_id = c.device_id
where university = '复旦大学';

使用多表连接实现,单独查询出正确答案数和做对题目数,使用左外连接,通过case when函数转换数据中的null值为0.