明确题意：

计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score； 按人数降序，相同人数的按平均分升序

问题分解：

  • 获取每张SQL类别试卷发布日期，作为子查询：
    • 筛选试卷类别：WHERE tag = "SQL"
    • 获取试卷ID和发布日期：SELECT exam_id, DATE(release_time)
  • 筛选发布当天的作答记录：WHERE (exam_id, DATE(start_time)) IN (...)
  • 筛选5级以上的用户：AND uid IN (SELECT uid FROM user_info WHERE level > 5)
  • 按试卷ID分组：GROUP BY exam_id
  • 计算作答人数：count( DISTINCT uid ) AS uv
  • 计算平均分（保留1位小数）：ROUND(avg( score ), 1) AS avg_score

细节问题：

  • 表头重命名：as
  • 按人数降序，按平均分升序：ORDER BY uv DESC, avg_score ASC

完整代码：

SELECT
    exam_id,
    count( DISTINCT uid ) AS uv,
    ROUND(avg( score ), 1) AS avg_score
FROM exam_record 
WHERE (exam_id, DATE(start_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 ASC;