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

select t00.device_id as device_id , t00.university as university,
       ifnull(t00.question_cnt,0) as question_cnt,
       ifnull(t01.right_question_cnt,0) as right_question_cnt
from (select t.device_id as device_id, t.university as university ,
count(*) as question_cnt
from user_profile t
left join (select  device_id, result, date
            from question_practice_detail
            where STR_TO_DATE(DATE_FORMAT(date, '%Y-%m-01'), '%Y-%m-%d') = '2021-08-01'
            ) t1
on t.device_id = t1.device_id
group by t.device_id , t.university ) t00
left join (
    select t.device_id as device_id, t.university as university ,
           count(*) as right_question_cnt
            from user_profile t
            left join (select  device_id, result, date
            from question_practice_detail
            where STR_TO_DATE(DATE_FORMAT(date, '%Y-%m-01'), '%Y-%m-%d') = '2021-08-01' 
            ) t1
            on t.device_id = t1.device_id
            where t1.result = 'right'
            group by t.device_id , t.university
)t01
on t00.device_id = t01.device_id  
where t00.university = '复旦大学'

MySQL的去空函数，IFNULL(字段名，0)，类似Oracle的NVL函数

MYSQL 不能像Oracle一样用trunc截断日期，可用STR_TO_DATE(DATE_FORMAT(date, '%Y-%m-01'), '%Y-%m-%d') = '2021-08-01'