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

# SELECT a.device_id,
#        a.university,
#        count(question_id) as question_cnt,
#        sum(if(b.result = 'right',1,0)) as right_question_cnt
# FROM user_profile a 
# LEFT JOIN question_practice_detail b ON a.device_id = b.device_id AND MONTH(`date`)=8
# WHERE a.university = "复旦大学"
# GROUP BY a.device_id;

11111
SELECT
	a.device_id,
	a.university,
	count(b.question_id) as question_cnt,
sum(case when result = 'right' then 1 else 0 end) as right_question_cnt
FROM
	user_profile AS a
left JOIN
	question_practice_detail AS b
ON
	a.device_id = b.device_id and month(date) = 8
where a.university = '复旦大学' 
group by device_id

22222
SELECT
	a.device_id,
	a.university,
	count(b.question_id) as question_cnt,
sum(case when result = 'right' then 1 else 0 end) as right_question_cnt
FROM
	user_profile AS a
left JOIN
	question_practice_detail AS b
ON
	a.device_id = b.device_id 
where a.university = '复旦大学' and month(date) = 8
group by device_id

仔细品味一下11111和22222的区别

11111：and month(date) = 8放在on后面

22222：and month(date) = 8放在where后面