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

子查询
select a.exam_id exam_id,count(distinct b.uid) uv,round(avg(b.score),1) avg_score
from examination_info a join exam_record b on a.exam_id=b.exam_id  and a.tag='SQL'
where b.uid in (select uid from user_info where level >5) and left(a.release_time,10)=left(b.start_time,10)
group by left(b.start_time,10),exam_id
order by uv desc , avg_score asc
联表
select a.exam_id exam_id,count(distinct b.uid) uv,round(avg(b.score),1) avg_score
from examination_info a join exam_record b on a.exam_id=b.exam_id and a.tag='SQL' and left(a.release_time,10)=left(b.start_time,10)
join user_info c on c.uid=b.uid and c.level>5
group by left(b.start_time,10),exam_id
order by uv desc , avg_score asc