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

select a.exam_id,count(distinct uid) as uv,round(sum(score)/count( uid),1) as avg_score
from examination_info a
join exam_record b using(exam_id)
where tag='SQL' and uid in (
    select a.uid
    from exam_record a
    join user_info using(uid)
    where level>5 
) and date_format(release_time,'%Y-%m-%d')=date_format(submit_time,'%Y-%m-%d')
group by exam_id
order by uv desc,avg_score;