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

浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。
输出：题目难度，正确率=正确题数/总题数

qd.difficult_lever
sum(if(qpd.result='right',1,0))/count(qpd.device_id) AS correct_rate

筛选：浙江大学

where u.university = '浙江大学'

分类：不同难度

GROUP BY qd.difficult_level

排序：按照正确率，升序排列

ORDER BY correct_rate

由于输出中正确率的参数主要来源于gpd因此联结时完整保留gpd信息

question_practice_detail AS qpd
LEFT JOIN user_profile AS u 
ON qpd.device_id = u.device_id 

LEFT JOIN question_detail AS qd
ON qpd.question_id = qd.question_id

用整理查询语句顺序得正确答案

SELECT qd.difficult_level,
       sum(if(qpd.result='right',1,0))/count(qpd.device_id) AS correct_rate
FROM
    (question_practice_detail AS qpd
    LEFT JOIN user_profile AS u 
    ON qpd.device_id = u.device_id 

    LEFT JOIN question_detail AS qd
    ON qpd.question_id = qd.question_id)
WHERE u.university = '浙江大学'
GROUP BY qd.difficult_level
ORDER BY correct_rate ASC;