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

select
    er.exam_id,
    count(distinct er.uid) as uv,
    round(avg(score),1) as avg_score
from exam_record er
inner join user_info ui
on ui.uid = er.uid 
inner join examination_info ei 
on ei.exam_id = er.exam_id 
where ei.tag='SQL' and ui.level>5
and  DATE_FORMAT(ei.release_time,'%Y%m%d') = DATE_FORMAT(er.submit_time,'%Y%m%d')
group by er.exam_id
order by uv desc, avg_score asc