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

#step3：根据需求挑选“经过条件筛选后的数据表”中，所需展示的字段#
select t2.exam_id,
count(distinct t2.uid) as uv,
round(avg(t2.score),1) as avg_score
#step1:先将所需的数据表进行关联#
from examination_info as t1
inner join exam_record as t2
on t1.exam_id = t2.exam_id
inner join user_info as t3
on t2.uid = t3.uid
#step2:按照条件对关联后的数据表进行条件筛选#
where t1.tag = 'SQL'
and t3.level > 5
and date_format(t1.release_time,'%Y%m&d') = date_format(t2.start_time,'%Y%m&d')
  #注意条件3含义转化：试卷发布当天用户作答人数 = 试卷发布日期=用户开始作答日期(具体到天)，然后对其进行计数#
#step4：对挑选出的展示字段，按照需求进行分组and排序#
group by 1
order by uv desc,avg_score;