题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
http://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
select
a.device_id,
a.university,
count(question_id) as question_cnt,
sum(if(result='right',1,0)) as right_question_cnt -- 也可以count搭配case when
from
(-- 筛选出复旦大学的
select
device_id,
university
from user_profile
where university='复旦大学') a
left join
(-- 筛选出8月份答题的
select
device_id,
question_id,
result
from question_practice_detail
where month(date)=8 ) b
on a.device_id=b.device_id
group by a.device_id, a.university
