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

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

sum(case when question_practice_detail.result = 'right' then 1 else 0 end) 这一部分是抄的答案 查答案是正确的题数量

我先考虑到对id分组 但是分组之前可以先用where 搜大学和时间

还有一个易错点是第一个搜索词select user_profile.device_id 这个地方前面只能是user_profile 不能是question_practice_detail