题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
http://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
没在表里看到日期相关的列。。。。。。还是看了解题思路才发现有个date
SELECT a.device_id
,a.university
,count(b.question_id ) question_cnt
,sum(case when b.result = 'right' then 1 else 0 end ) right_question_cnt
from user_profile a
left join question_practice_detail b
on a.device_id = b.device_id
where a.university = '复旦大学' and MONTH(date) = 8
group by a.device_id,a.university