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

select a.device_id,university,
count(question_id) as question_cnt,
sum(if(b.result='right',1,0)) 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 university = "复旦大学"
group by a.device_id

1.正确函数是MONTH(date)

2.在on语句中加条件查询month(date)=8

3.单引号''：字符串和日期类型的数据值使用单引号''引起来，数值类型的不需要加标点符号。

双引号""：列的别名可以使用双引号""，给表名取别名==不要使用==双引号。

4.CASE WHEN b.result ='right'THEN 1 ELSE 0 END

5.(value,t,f)

如果value是真，返回t,否则返回f