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

先把需要的东西罗列出来

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

其次，分析条件

1.用户等级>5, 2.类别为sql 3.发布当天作答

解：

uid 在用户表中找，等级需要>5

select distinct uid  from user_info where  level >5

类别为sql，找出试卷的id

select distinct exam_id from examination_info where tag='SQL'

发布当天作答

select distinct date(release_time) from examination_info where tag='SQL' 

明确从哪个表中查，最后的代码

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

需要注意的细节： 小数点后1位

多条件排序：order by uv desc, avg_score ASC

去重！去重！去重！尤其是用到in时