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

select
    t1.exam_id as exam_id,
    count(distinct t1.uid) as uv,
    round(avg(t1.score), 1) as avg_score
from
    exam_record as t1
    inner join user_info as t2 on t1.uid = t2.uid
    inner join examination_info as t3 on t1.exam_id = t3.exam_id
where
    t2.level > 5
    and date_format (t3.release_time, '%Y%m%d') = date_format (t1.start_time, '%Y%m%d')
group by t1.exam_id
order by uv desc, avg_score asc;