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

-- 解法一 子查询
 select 
 exam_id
 ,count(distinct uid) as uv
 ,round(avg(score),1) avg_score
 from exam_record
 where (exam_id,date(submit_time)) in (
                                    select exam_id,date(release_time)
                                    from examination_info
                                    where tag='SQL'
                                     )
 and uid in (
             select uid
             from user_info
             where level >5
 )
 group by exam_id
 order by uv desc,avg_score

-- 解法二 多表连接
select
er.exam_id
,count(distinct uid) as uv
,round(avg(score),1) avg_score
from exam_record er

inner join (
            select exam_id,date(release_time) dt
            from examination_info
            where tag='SQL'
            ) ei
on date(er.submit_time)=ei.dt
and er.exam_id=ei.exam_id

inner join (
            select uid 
            from user_info
            where level>5
            ) ui
using(uid)
group by er.exam_id
order by uv desc,avg_score

-- 解法四

	
	

		select
	

	

		exam_id
	

	

		,count(distinct uid) as uv
	

	

		,round(avg(score),1) avg_score
	

	

		from exam_record er
	

	

		left join user_info ui using(uid)
	

	

		left join examination_info ei using(exam_id)
	

	

		where level>5 
	

	

		and date(submit_time)=date(release_time)
	

	

		and tag='SQL'
	

	

		group by exam_id
	

	

		order by uv desc,avg_score
	

根据最后的解释我们可以得到以上图，筛选出submit_time 等于sql的release_time，等级大于5的uid，exam_id 等于sql的exam_id,这些筛选条件都在另外的表，因此解法一通过where 子查询 ；解法二 可以通过表连接；解法三：混搭；解法四：先三表连起来，再筛选