题解 | #浙大不同难度题目的正确率#

select t00.difficult_level as difficult_level,
ifnull(t01.countr/t00.countz,0) as correct_rate
from (
    select t2.difficult_level as difficult_level,count(t0.university) as countz
    from  question_detail t2
    left join question_practice_detail t1
    on t1.question_id = t2.question_id
    left join   user_profile t0
    on t0.device_id = t1.device_id
    and t0.university = '浙江大学'
    group by t2.difficult_level
)t00  
left join 
(   select t2.difficult_level as difficult_level,count(*) as countr
    from  question_detail t2
    left join question_practice_detail t1
    on t1.question_id = t2.question_id
    left join   user_profile t0
    on t0.device_id = t1.device_id
    where t0.university = '浙江大学'
    and t1.result = 'right'
    group by t2.difficult_level
)t01 
on t00.difficult_level = t01.difficult_level
order by correct_rate

联表操作时注意哪个应为主表