题解 | #统计复旦用户8月练题情况#(需要优化)

# 1.先查出每个用户8月答题数量、正确的数量，作为表a
# 2.再去用户表中找出复旦大学的学生，与表a进行左外连接

select 
    c.device_id,
    c.university,
    if(d.question_cnt is NULL,0,d.question_cnt) question_cnt,
    if(d.right_question_cnt is NULL,0,d.right_question_cnt) right_question_cnt

from user_profile as c
left join
    (select a.device_id,a.question_cnt,b.right_question_cnt
        from 
            (select device_id,count(*) as question_cnt
            from question_practice_detail
            where substring(date,7,1)=8
            group by device_id) as a
        left join (
            select device_id,count(*) as right_question_cnt
            from question_practice_detail
            where result="right" and substring(date,7,1)=8
            group by device_id
        )as b
        on a.device_id = b.device_id
    ) as d
on c.device_id = d.device_id
where c.university="复旦大学"

这个代码写的又臭又长，需要优化