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

select 
    device_id,
    university, 
    sum(if(result is not null and month(date)=8,1,0)) as question_cnt,
    sum(if(result='right' and month(date)=8,1,0)) as right_question_cnt
    from 
        (select
            u.device_id,u.university,qpd.result,qpd.date
        from
        user_profile u
        left join
        question_practice_detail qpd 
        on u.device_id = qpd.device_id 
        where u.university='复旦大学') as t
group by device_id;

首先，不能把8月没有答题的用户筛选掉，因此要使用左查询，生成的表如下：

其次，按device_id分别统计。其中8月总答题数量：sum(if(result is not null and month(date)=8,1,0))

8月份回答正确的题目数量： sum(if(result='right' and month(date)=8,1,0))