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

SELECT a.exam_id, COUNT(DISTINCT(uid)) uv, ROUND(AVG(score),1) avg_score
FROM exam_record a JOIN(
	SELECT exam_id, DATE_FORMAT(release_time, '%Y%m%d') release_data FROM examination_info WHERE tag = 'SQL'
) b ON a.exam_id = b.exam_id AND DATE_FORMAT(a.start_time,'%Y%m%d') = release_data
WHERE a.uid IN (SELECT uid FROM user_info WHERE `level` > 5)
GROUP BY exam_id
ORDER BY uv DESC, avg_score;