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

select exam_id,count(distinct uid) as uv,
       round(avg(score),1) as avg_score
from
    ( select ui.uid,results_no5.exam_id,results_no5.score
    from (SELECT ei.exam_id, ei.release_time, ei.tag, er.uid, er.start_time, er.score
          FROM examination_info ei,
               exam_record er
          WHERE ei.tag = 'SQL'
            AND DATE(ei.release_time) = DATE(er.start_time)
   and ei.exam_id=er.exam_id)
        as results_no5,user_info as ui
        where ui.level>5 and ui.uid=results_no5.uid
    ) as result_5
group by exam_id
ORDER BY uv DESC ,avg_score asc;