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

• "复旦大学" as university 起别名的巧用
• sum(if(qpd.result = 'right', 1, 0)) 单行函数的嵌套
• 日期函数 month(qpd.date) = 8

方式一：先在question_practice_detail表中查询month(date) = 8的用户的device_id等信息作为新的一张表，再与user_profile进行左连接，筛选出university = '复旦大学'。但是这种写法，需要我们额外的对question_cnt，right_question_cnt字段进行判空处理

# 查询8月份刷题的用户和刷题量
select
  up.device_id,
  university,
  (
    case
      when qpd.question_cnt is null then 0
      else qpd.question_cnt
    end
  ) question_cnt,
  (
    case
      when right_question_cnt is null then 0
      else right_question_cnt
    end
  ) right_question_cnt
from
  user_profile up
  left outer join (
    select
      device_id,
      count(question_id) question_cnt,
      sum(if(result = 'right', 1, 0)) right_question_cnt
    from
      question_practice_detail
    where
      month(date) = 8
    group by
      device_id
  ) qpd on up.device_id = qpd.device_id
where
  university = '复旦大学'

方式二: user_profile与question_practice_detail进行连接的时候，就在on中选出month(qpd.date) = 8的数据，在where中选出university = "复旦大学"

select
up.device_id,
"复旦大学" as university,
count(question_id) as question_cnt,
sum(if(qpd.result = 'right', 1, 0)) as right_question_cnt
from
user_profile up
left join question_practice_detail qpd on up.device_id = qpd.device_id
and month(qpd.date) = 8
where
university = "复旦大学"
group by
device_id