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

select er.exam_id,count(distinct er.uid) as uv,round(avg(score),1) as avg_score
from exam_record as er
left join examination_info as ei using(exam_id)
left join user_info as ui using (uid)
where er.uid in
(
select er.uid
from exam_record as er
left join examination_info as ei using(exam_id)
left join user_info as ui using (uid)
where date_format(start_time,'%Y-%m-%d') in
(
select date_format(release_time,'%Y-%m-%d') as rt
from examination_info
where tag='SQL'
)
and level>5
)
and tag='SQL'
group by er.exam_id
order by uv desc,avg_score asc