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

#先把需要的数据连接在一起，

select a.uid,a.exam_id,a.score,

DATE_FORMAT(a.start_time,'%Y-%m-%d')start_time,

DATE_FORMAT(a.submit_time,'%Y-%m-%d')submit_time,

DATE_FORMAT(b.release_time,'%Y-%m-%d')release_time,c.level

from

exam_record a

join

examination_info b

on a.exam_id=b.exam_id

join

user_info c

on a.uid=c.uid;

#根据上面连接在一起的数据分组查询即可

select d.exam_id,count(distinct d.uid)uv,round(avg(d.score),1)avg_score

from

(select a.uid,a.exam_id,a.score,

DATE_FORMAT(a.start_time,'%Y-%m-%d')start_time,

DATE_FORMAT(a.submit_time,'%Y-%m-%d')submit_time,

DATE_FORMAT(b.release_time,'%Y-%m-%d')release_time,c.level

from

exam_record a

join

examination_info b

on a.exam_id=b.exam_id

join

user_info c

on a.uid=c.uid)d

where d.start_time=d.release_time and d.level >5 and d.submit_time is not null

group by d.exam_id

order by uv desc,avg_score asc;