如何在SQL中基于平均分区间实现学生排名系统？

要实现按平均分整数区间（如90-90.9归为90区间、91-91.9归为91区间）的排名逻辑，核心是先对区间分组再排名，而非直接对单个分数排名。你的原查询用DENSE_RANK()直接对每个学生的唯一分数排名，因此无法实现区间合并的需求。

实现步骤与SQL代码 #

WITH StudentAverages AS (
  -- 第一步：计算每个学生的平均分，并提取区间标识（取平均分的整数部分）
  SELECT
    student_id,
    CAST(AVG(ft_tot_score) AS DECIMAL(10, 2)) AS avg_score,
    FLOOR(AVG(ft_tot_score)) AS score_interval -- 91.8和91.1的FLOOR结果都是91，属于同一区间
  FROM
    ftscores_primary
  WHERE
    class_id = 9 AND
    section_id = 3 AND
    session_id = 19
  GROUP BY
    student_id
),
IntervalRanks AS (
  -- 第二步：对所有不同的区间去重，按区间从高到低分配排名
  SELECT
    score_interval,
    DENSE_RANK() OVER (ORDER BY score_interval DESC) AS interval_rank
  FROM
    StudentAverages
  GROUP BY
    score_interval -- 去重，确保每个区间只生成一个排名
)
-- 第三步：关联学生数据与区间排名，处理排名后缀
SELECT
  sa.avg_score,
  CASE
    WHEN ir.interval_rank = 1 THEN '1st'
    WHEN ir.interval_rank = 2 THEN '2nd'
    WHEN ir.interval_rank = 3 THEN '3rd'
    ELSE
      CASE
        WHEN RIGHT(ir.interval_rank, 1) = 1 AND ir.interval_rank != 11 THEN CONCAT(ir.interval_rank, 'st')
        WHEN RIGHT(ir.interval_rank, 1) = 2 AND ir.interval_rank != 12 THEN CONCAT(ir.interval_rank, 'nd')
        WHEN RIGHT(ir.interval_rank, 1) = 3 AND ir.interval_rank != 13 THEN CONCAT(ir.interval_rank, 'rd')
        ELSE CONCAT(ir.interval_rank, 'th')
      END
  END AS position
FROM
  StudentAverages sa
JOIN
  IntervalRanks ir ON sa.score_interval = ir.score_interval
ORDER BY
  sa.avg_score DESC;

关键逻辑说明 #

1. 区间分组：用FLOOR(AVG(ft_tot_score))提取平均分的整数部分，自动把[n, n+1)区间内的分数归为同一组（比如91.0到91.999都归为91区间），完全匹配你要求的n-n.9区间规则。
2. 区间排名：对去重后的区间使用DENSE_RANK()，确保每个区间获得唯一排名，同一区间内的学生共享该排名。
3. 排名后缀处理：保留你原有的后缀生成逻辑，替换为区间排名即可。

示例验证匹配 #

• 91.80和91.10的score_interval都是91，会被分配同一个区间排名（如第4名）
• 89.20的score_interval是89，获得下一个排名（第5名）
• 86.90、86.80、86.00的score_interval都是86，共享第6名

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