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

select t.exam_id,count(distinct e1.uid) as uv 
,round(avg(e1.score),1) as avg_score
from exam_record e1 
join 
(
    select exam_id,release_time
    from examination_info
    where tag="SQL"
) t
on t.exam_id = e1.exam_id
join user_info e2 
on e1.uid=e2.uid
where e2.level>5
and date_format(t.release_time,"%Y%m%d")=date_format(e1.start_time,"%Y%m%d")
group by t.exam_id
order by uv desc,avg_score