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

SELECT record.exam_id as exam_id,count(DISTINCT record.uid) as uv,ROUND(avg(score),1) as avg_score FROM user_info AS user LEFT JOIN exam_record AS record on user.uid=record.uid LEFT JOIN examination_info AS info ON info.exam_id=record.exam_id WHERE user.level>5 AND info.tag IS NOT NULL AND DATEDIFF(info.release_time,record.start_time)=0 AND  TIMESTAMPDIFF(second,info.release_time,record.start_time)>=0 AND record.submit_time IS NOT NULL AND record.score IS NOT NULL GROUP BY exam_id ORDER BY uv DESC, avg_score ASC;
#DATE_FORMAT(record.start_time,"%Y%m%d")=DATE_FORMAT(info.release_time,'%Y%m%d') 这个可以用来代替这个datediff进行这个字符串比较也行但是这个没有这个上面的效率高这个是需要这个进行这个转换