题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
首先 找到两个表的联系，得知是 device_id 用左连接
这里得分两种case：
1、空的 （）
2、包括8月 （说明：8月可能有练习的，也有没有练习的）
然后利用where 将两种情况分开
#case1:
select p1.device_id,p1.university,count(date) as question_cnt,
sum(if(p2.result="wrong",0,1)) as right_question_cnt
from user_profile as p1 left join question_practice_detail as p2
on p1.device_id=p2.device_id
where p1.university="复旦大学" and date is not null and month(date)=8
group by p1.device_id
union
from user_profile as p1 left join question_practice_detail as p2
on p1.device_id=p2.device_id
where p1.university="复旦大学" and date is not null and month(date)=8
group by p1.device_id
union
#case2
select p1.device_id,p1.university,count(date) as question_cnt,
sum(if(p2.result is null,0,0)) as right_question_cnt
from user_profile as p1 left join question_practice_detail as p2
on p1.device_id=p2.device_id
where p1.university="复旦大学" and date is null
group by p1.device_id
select p1.device_id,p1.university,count(date) as question_cnt,
sum(if(p2.result is null,0,0)) as right_question_cnt
from user_profile as p1 left join question_practice_detail as p2
on p1.device_id=p2.device_id
where p1.university="复旦大学" and date is null
group by p1.device_id
查看9道真题和解析