题解 | 试卷平均分 公式和理解有差异

知识点: 1.多表联接,这块用内连接效率会更高; 2. 日期函数的使用

SELECT er.exam_id, 
	   count(distinct er.uid) as uv,
       ROUND(AVG(score),1) avg_score 
       # sum(score)/count(distinct er.uid) 出错原因是因为当天一个人可能会做多次,且所求为试卷平均分count(1)即可
FROM exam_record er left join 
	 examination_info ei on er.exam_id = ei.exam_id 
     join 
     user_info ui on er.uid=ui.uid
WHERE level >5 and date(start_time)=date(release_time) and tag='SQL'
#或者用DATE_FORMAT(submit_time,'%y%m%d')=DATE_FORMAT(release_time,'%y%m%d')
GROUP BY er.exam_id
order by uv desc,avg_score asc;