题解 | #试卷发布当天作答人数和平均分#

# SQL类别试卷, 试卷ID, 发布当天作答人数，用户等级>5, 人数uv(去重), 平均分avg_score
SELECT a.exam_id, COUNT(DISTINCT a.uid) AS uv, ROUND(AVG(a.score),1)AS avg_score
FROM
    (SELECT i.exam_id, r.uid, r.score
    FROM examination_info AS i LEFT JOIN exam_record AS r ON i.exam_id = r.exam_id
    WHERE i.tag='SQL' 
    AND DATE_FORMAT(i.release_time, '%Y%m%d') = DATE_FORMAT(r.start_time, '%Y%m%d') 
    ) AS a LEFT JOIN user_info AS u ON a.uid = u.uid
WHERE u.level > 5
GROUP BY a.exam_id
ORDER BY uv DESC, avg_score;