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

# 最外层聚合查询出最终的结果
select
    exam_id,
    count(distinct uid) as uv,
    round(avg(score), 1) as avg_score
from
    exam_record
where
	# 子查询1，用于查出符合的exam号和提交日期
    (exam_id, date_format (submit_time, '%Y%m%d')) in (
        select
            exam_id,
            date_format (release_time, '%Y%m%d')
        from
            examination_info
        where
            tag = 'SQL'
    )
	# 子查询2 用于查出符合条件的 uid
    and uid in (
        select
            uid
        from
            user_info
        where
            level > 5
    )
group by
    exam_id
order by
    count(distinct uid) desc,
    avg_score asc

这题一个核心的思想就是可以有多个并列的子查询来过滤出符合条件的数据，然后再整合起来查出最终的结果