题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
#主要是分组和时间处理
select
p.device_id,
p.university,
count(q.result) as total,
sum(
case
when q.result = 'right' then 1 else 0
end
) as p
from
user_profile p
left join question_practice_detail q on p.device_id = q.device_id
where
p.university = '复旦大学'
and (8 = MONTH (q.date) or q.date is null)
group by
p.device_id