难题但对丨方法总结
浙大不同难度题目的正确率
https://www.nowcoder.com/practice/d8a4f7b1ded04948b5435a45f03ead8c?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Foj%3Ftab%3DSQL%25E7%25AF%2587%26topicId%3D199
秘诀在于拆分问题，将浙江大学的用户在不同难度题目下答题的正确率情况 拆分成：
①浙江大学的用户在不同难度题目数 ②不同难度题目下答题的正确
再把他们进行相除即可
SELECT
d.difficult_level,
d.浙大各难度正确数/e.浙大各难度题目数 正确率
FROM(SELECT
c.difficult_level,
sum(if(b.result='right',1,0))浙大各难度正确数
FROM user_profile a
LEFT JOIN question_practice_detail b
on a.device_id=b.device_id
AND a.university='浙江大学'
LEFT JOIN question_detail c
on b.question_id=c.question_id
where c.difficult_level IS NOT null
GROUP BY difficult_level)d
LEFT JOIN (SELECT
c.difficult_level,
COUNT(*)浙大各难度题目数
FROM user_profile a
LEFT JOIN
question_practice_detail b
ON a.device_id=b.device_id
AND a.university='浙江大学'
LEFT JOIN
question_detail c
ON b.question_id=c.question_id
WHERE difficult_level is NOT NULL
GROUP BY c.difficult_level)e
on d.difficult_level=e.difficult_level
ORDER BY 正确率
