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

#做的总题目: COUNT(QPD.question_id)
#正确题目: SUM( IF( result = 'right', 1, 0) )

#内链接：
# SELECT QD.difficult_level, 
#         (SUM( IF( result = 'right', 1, 0) )/COUNT(QPD.question_id))  AS correct_rate
# FROM    question_practice_detail AS QPD,
#         user_profile AS UP,
#         question_detail AS QD

# WHERE   UP.university = '浙江大学'
# AND     QPD.question_id = QD.question_id
# AND     UP.device_id = QPD.device_id

# GROUP BY QD.difficult_level
# ORDER BY correct_rate ASC;

#外链接
SELECT QD.difficult_level, 
#        (SUM( IF( result = 'right', 1, 0) )/COUNT(QPD.question_id))  AS correct_rate
         AVG( IF( result = 'right', 1, 0)) AS correct_rate
FROM    question_practice_detail AS QPD

LEFT JOIN     user_profile AS UP
ON QPD.device_id = UP.device_id

LEFT JOIN     question_detail AS QD
ON QPD.question_id = QD.question_id

WHERE UP.university = '浙江大学'
GROUP BY QD.difficult_level
ORDER BY correct_rate ASC;