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

-- 1、测试记录表关联试卷表查出试卷发布当天的记录，关联人员表    
-- 2、过滤条件  
-- 3、分组统计，试卷id，分组函数 COUNT(DISTINCT a.uid) uv,SUM(a.score) /  COUNT(a.exam_id)
  
SELECT 
    a.exam_id,
    COUNT(DISTINCT a.uid) uv,
    ROUND(SUM(a.score) / COUNT(a.exam_id), 1) avg_score
FROM
    exam_record a
        INNER JOIN
    examination_info b ON a.exam_id = b.exam_id
        AND DATE(a.submit_time) = DATE(b.release_time)
        INNER JOIN
    user_info c ON a.uid = c.uid
WHERE
    c.level > 5 AND b.tag = 'SQL'
GROUP BY a.exam_id
ORDER BY uv DESC , avg_score ASC;