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

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