一、需求分析

• 计算当天5级以上SQL类别的试卷的作答人数UV，平均分avg_score
• 输出：exam_id,uv,avg_score （order by uv desc,avg_score asc)

二、思路

1. 作答信息新增字段作答日期，内连接试卷表，选择考试tag为sql且试卷发行日期与作答日期相同的记录
2. 内连接用户信息表，筛选等级>5的用户
3. 处理输出结果：字段顺序，小数点，排序

三、代码实现

select t1.exam_id,count(distinct t1.uid) uv,round(avg(t1.score),1) avg_score
from 
    (select distinct *,date_format(submit_time,'%Y-%m-%d') t_r  # 作答时间
        from exam_record)t1
    inner join 
    (select DISTINCT exam_id,date_format(release_time,'%Y-%m-%d') t_i 
        from examination_info 
        where tag = 'SQL') t2
    on t1.exam_id = t2.exam_id and t1.t_r = t2.t_i
    inner join (select distinct uid from user_info where level >5) t3
    on t1.uid = t3.uid
group by t1.exam_id
order by uv desc,avg_score asc