如何基于平均分对学生进行降序排名？SQL查询需求

修改后的查询代码 #

假设你的student_subject_averages表中存储平均分的字段名为average_score（请根据实际表结构调整），可以通过窗口函数实现按平均分降序的排名。以下是三种常用的排名方式，你可以根据需求选择：

SELECT 
    ssa.*,
    -- 方式1：同分同排名，后续排名跳号
    RANK() OVER (ORDER BY ssa.average_score DESC) AS score_rank
    -- 方式2：同分同排名，后续排名不跳号
    -- DENSE_RANK() OVER (ORDER BY ssa.average_score DESC) AS score_rank
    -- 方式3：同分也会生成唯一连续排名
    -- ROW_NUMBER() OVER (ORDER BY ssa.average_score DESC) AS score_rank
FROM student_subject_averages ssa
WHERE ssa.teaching_load_id IN (
    SELECT id 
    FROM teaching_loads 
    WHERE teacher_id = 1573 
      AND subject_id = 24 
      AND class_id IN (
          SELECT id 
          FROM grades 
          WHERE stream_id = 4
      )
)
ORDER BY ssa.average_score DESC;

三种排名函数的差异 #

• RANK()：如果多个学生平均分相同，他们会获得相同的排名，下一个不同分数的学生排名会跳过已占用的位次。例如两个学生排第1，下一个学生直接排第3。
• DENSE_RANK()：同分学生共享同一排名，后续排名不会跳号。例如两个学生排第1，下一个学生排第2。
• ROW_NUMBER()：无论分数是否相同，每个学生都会得到唯一的连续排名。如果分数相同，排序顺序取决于数据库的默认规则，你可以额外添加排序字段（如学生ID）来确保一致性：

  ROW_NUMBER() OVER (ORDER BY ssa.average_score DESC, ssa.student_id ASC) AS score_rank
  

注意点 #

• 请将代码中的average_score替换为你的表中实际存储平均分的字段名称。
• 若需要更复杂的排序逻辑，可在OVER()的ORDER BY子句中添加更多字段。

内容的提问来源于stack exchange，提问作者Ation