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

一直嵌套引用表格，慢慢调试，就行了... 感觉解的并不是很漂亮

SELECT dt3.id,dt3.uni,
count( case when month(dt3.date)=8 then 1 else null end
     ),
count( case when dt3.res = 'right' then 1 else null end
     )
From
(
SELECT up.device_id as id,up.university as uni,
        qpd.date as date,qpd.result as res
From user_profile as up
LEFT JOIN
(SELECT device_id,date,result
From question_practice_detail) as qpd 
on up.device_id = qpd.device_id
where up.university="复旦大学"  
)  as dt3
group by dt3.id