# 首先看需要哪些列 exam_id ,作答人数，平均分
# 限制为SQL类型的试卷 按exam_id 进行分组
# 对uid进行限制 5级以上 SQL发布当天的作答
select exam_id, COUNT(distinct er.uid) AS uv,
ROUND(AVG(er.score),1) AS avg_score
FROM user_info AS ui
JOIN exam_record AS er using(uid)
JOIN examination_info AS ei using(exam_id)
WHERE
ei.tag = 'SQL'
AND #等级限制
ui.level > 5
AND #SQL发布当天的作答的用户
date_format(ei.release_time,'%Y%m%d') = date_format(er.submit_time,'%Y%m%d')
GROUP BY exam_id
ORDER BY uv DESC,avg_score
# 子查询
# 首先看需要哪些列 exam_id ,作答人数，平均分
# 限制为SQL类型的试卷 按exam_id 进行分组
# 对uid进行限制 5级以上 对时间限制 SQL发布当天的作答
select exam_id,COUNT(distinct uid) AS uv,
ROUND(AVG(score), 1) AS avg_score
FROM exam_record
WHERE uid in(
select uid FROM user_info
WHERE level > 5
) AND exam_id in (
select exam_id FROM examination_info
WHERE tag = 'SQL'
)
AND date_format(start_time,'%Y%m%d') in (
select date_format(release_time,'%Y%m%d')
FROM examination_info
WHERE tag = 'SQL'
)
GROUP BY exam_id
ORDER BY uv DESC, avg_score ASC
