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

SELECT exam_id,count(distinct uid) uv,round(avg(score),1) avg_score from (
		SELECT er.*,ei.tag,ei.difficulty from exam_record er
		join examination_info ei on er.exam_id=ei.exam_id where tag = 'SQL' and DATE_FORMAT(er.submit_time,"%Y%m%d")=DATE_FORMAT(ei.release_time,"%Y%m%d") and
		uid in (
				SELECT uid FROM user_info where level>5)
)t1 
GROUP BY tag,exam_id order by uv desc,avg_score asc;