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

结果

select up.device_id,
        university,
        ifnull(qpd.question_cnt,0) as question_cnt,
        ifnull(qpd.right_question_cnt,0) as right_question_cnt
from user_profile up left join
(   select device_id,
        count(question_id) as question_cnt,
        sum(if(result='right',1,0)) as right_question_cnt
    from question_practice_detail
    where date_format(date,'%Y-%m')='2021-08'
    group by device_id) as qpd
on up.device_id = qpd.device_id
where university="复旦大学"

过程

一步一步来 从题目中提取关键字：复旦大学、每个用户、八月份、总题目数、正确题目数 ，分解步骤

(1)先从question_practice_detail表中 找出八月份 每个用户的答的总题目数和正确题目数 作为表qpd

select device_id,
       count(question_id) as question_cnt,
       sum(if(result='right',1,0)) as right_question_cnt
from question_practice_detail
where date_format(date,'%Y-%m')='2021-08'
group by device_id

(2)将user_profile表与qpd进行左连接，选出学校等于复旦大学的用户记录

select up.device_id,
        university,
        ifnull(qpd.question_cnt,0) as question_cnt,
        ifnull(qpd.right_question_cnt,0) as right_question_cnt
from user_profile up left join
(   select device_id,
        count(question_id) as question_cnt,
        sum(if(result='right',1,0)) as right_question_cnt
    from question_practice_detail
    where date_format(date,'%Y-%m')='2021-08'
    group by device_id) as qpd
on up.device_id = qpd.device_id
where university="复旦大学"