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

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

思路：

1查询question_practice_detail中8月份的数据，按用户分组，统计答题数，统计正确数

2.user_profile表限定条件复旦大学

3question_practice_detail进行左连接

注意question_practice_detail作为左连接的表

注释部分写反了，导致结果会有问题