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

SELECT 
device_id,
university,
COUNT(*),
COUNT(
CASE 
	WHEN
		result="right"   
	THEN
		1
	ELSE
		NULL
END) 
FROM 
(
SELECT u.device_id,university,result
FROM user_profile u
LEFT JOIN question_practice_detail q
ON
u.device_id=q.device_id
WHERE result IS NOT NULL
AND university="复旦大学"
) t
GROUP BY device_id,university