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

select
    examination_info.exam_id as exam_id,
    count(distinct uid) as uv,
    round(avg(score),1) as avg_score
    
    
from
examination_info inner join
exam_record
on examination_info.exam_id = exam_record.exam_id 
and date(submit_time) = date(release_time)
where uid in
(select
    uid
from 
user_info
where level > 5)
group by (exam_id)
order by uv desc , avg_score

首先从user_info表中拿出5级以上的uid

    uid
from 
user_info
where level > 5)

之后连接examination_info和exam_record表，找到发布当天的试卷及其答题详情 再从得到的uid中拿到符合的行即 大于5级且是发布当天的答题记录 按试卷号来进行分组，计算其行数和平均数