过滤取值后再返回初始表格取值

当两个临时表在联结时都包含同一个过滤规则时，将这个规则提取到外面来写，而不是分别写在两个临时表代码中。

select
    uni_de.device_id, 
    uni_de.university,
    case when question_cnt is null then 0 else question_cnt end as q_cnt,
    case when right_cnt is null then 0 else right_cnt end as r_cnt
from 
-- 先把复旦大学的所有学生的id找出来并放在一边
    (select distinct device_id ,university 
     from user_profile where university='复旦大学') as uni_de
left outer join
-- 再按照要求检索数据
    (select 
        a.device_id as device_id, 
        university, 
        count(question_id) as question_cnt,
        sum(case when result='right' then 1 else 0 end)as right_cnt
    from 
        user_profile as a 
        left join question_practice_detail as b
        on a.device_id = b.device_id
    where
        university = '复旦大学'
        and date like '%08%'
    group by a.device_id) as c
on 
    uni_de.device_id = c.device_id;

看看别人的答案。

select up.device_id, up.university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up

left join question_practice_detail as qpd
  on qpd.device_id = up.device_id and month(qpd.date) = 8

where up.university = '复旦大学'
group by up.device_id

在这里有很多问题，我不是很明白，又有点明白。但是还是学习到了一点：

当两个临时表在联结时都包含同一个过滤规则时，将这个规则提取到外面来写，而不是分别写在两个临时表代码中。