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

SELECT 
	t1.device_id,
    t1.university,
    COUNT(t1.question_id) AS question_cnt,
    SUM(result_1) AS right_question_cnt
FROM
	(SELECT 
	 -- 有重复字段名id,这里应该避免全选,否则后面会出现duplicate_columns错误
		u.device_id,
		u.university,
		d.question_id,
		d.result,
		d.date,
	 -- 把答错的记为0,答对的记为1,便于后面进行right_question_cnt的计算
		CASE WHEN result = 'wrong' THEN  0
			WHEN result = 'right' THEN 1
		END AS result_1
	FROM user_profile u 
	JOIN question_practice_detail d USING(device_id)) AS t1
-- 只需要计算复旦8月的答题情况
WHERE t1.university = '复旦大学' AND MONTH(t1.date) = 8
GROUP BY t1.device_id
UNION
SELECT 
	t2.device_id, t2.university,
    t2.question_cnt, t2.right_question_cnt
FROM 
	(SELECT 
		u.device_id,
		u.university,
		d.question_id,
		d.result,
		d.date,
		CASE WHEN question_id IS NULL THEN  0
		END AS question_cnt,
		CASE WHEN question_id IS NULL THEN  0
		END AS right_question_cnt
	FROM user_profile u
	LEFT JOIN question_practice_detail d USING(device_id)
	 -- 统计未回答问题的复旦用户情况
	WHERE u.university = '复旦大学' AND question_id IS NULL) AS t2;