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

select
t.exam_id,count(distinct t.uid) as uv,round(avg(t.score),1) avg_score
from
(select 
submit_time,er.uid,er.exam_id,date(release_time) as date1,score
from exam_record er 
left join user_info ui
on er.uid=ui.uid
left join examination_info ei
on er.exam_id=ei.exam_id
where tag='SQL'and level>5) t  #筛选出五级以上用户和tag为sql的条目
where date(t.submit_time)=t.date1
group by t.exam_id
order by uv desc,avg_score

脑子有点笨，习惯一步一步建立子查询，而且还得一步一步debug