#首先用左连接制作提取表，把所取字段提取建表，建表时需注意关注表中用户是否存在，附加日期筛选可避免遗漏用户 select user_profile.device_id,user_profile.university,question_practice_detail.question_id, question_practice_detail.result,question_practice_detail.date from user_profile left join question_practice_detail on question_practice_detail.device_id = user_profile.device_id and month(question_practice_detail.date) = 8) cnm

#在做提取答题结果字段时用case做判读求和 sum(case when cnm.result = 'right' then 1 else 0 end) right_question_cnt

#完整代码

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