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

SELECT
    q2.difficult_level,
    sum(if (q1.result = 'right', 1, 0)) / count(q1.question_id) as correct_rate
FROM
    user_profile u
    right join question_practice_detail q1 on u.device_id = q1.device_id
    left join question_detail q2 on q1.question_id = q2.question_id
WHERE
    university = '浙江大学'
GROUP BY
    q2.difficult_level
ORDER BY
    correct_rate;

思路：

1，浙大，这是条件，用where控制

WHERE
    university = '浙江大学'

2，不同难度，这是分组，用group by

GROUP BY
    q2.difficult_level

3，查表，关联三张表（其实往往从这一步开始写），用from和join连接

FROM
    user_profile u
    right join question_practice_detail q1 on u.device_id = q1.device_id
    left join question_detail q2 on q1.question_id = q2.question_id

4，输出需要的列，用select

5，计算正确率=总的right/总的question_id

SELECT
    q2.difficult_level,
    sum(if (q1.result = 'right', 1, 0)) / count(q1.question_id) as correct_rate

6，排序，用order by

ORDER BY
    correct_rate;