如何基于tblGrade参数为tblStudentGrade匹配生成学生成绩等级

需求概述 #

现有两张数据表：

• tblGrade：定义成绩等级的分配规则，Allocate Qty字段为0时，表示该等级分配给剩余所有学生
• tblStudentGrade：存储学生ID和对应分数，需要基于规则生成包含Grade字段的结果表

表结构及示例数据 #

tblGrade（等级分配规则表） #

tblStudentGrade（学生成绩表，含目标结果示例） #

SQL实现方案 #

以下是基于窗口函数的通用SQL写法（兼容MySQL 8.0+、PostgreSQL、SQL Server等支持CTE和窗口函数的数据库）：

WITH ranked_students AS (
    -- 按分数降序给学生排名，分数相同按行号区分（可根据需求替换为RANK()/DENSE_RANK()）
    SELECT 
        StudentID,
        Score,
        ROW_NUMBER() OVER (ORDER BY Score DESC) AS student_rank
    FROM tblStudentGrade
),
grade_boundaries AS (
    -- 计算非0分配量等级的区间范围
    SELECT 
        Grade,
        SUM(`Allocate Qty`) OVER (ORDER BY Rank) AS upper_limit,
        COALESCE(LAG(SUM(`Allocate Qty`) OVER (ORDER BY Rank)) OVER (ORDER BY Rank), 0) + 1 AS lower_limit
    FROM tblGrade
    WHERE `Allocate Qty` > 0
    -- 合并0分配量等级的区间（覆盖剩余所有学生）
    UNION ALL
    SELECT 
        Grade,
        (SELECT COUNT(*) FROM tblStudentGrade) AS upper_limit,
        (SELECT SUM(`Allocate Qty`) FROM tblGrade WHERE `Allocate Qty` > 0) + 1 AS lower_limit
    FROM tblGrade
    WHERE `Allocate Qty` = 0
)
-- 关联学生排名和等级区间，得到最终结果
SELECT 
    rs.StudentID,
    rs.Score,
    gb.Grade
FROM ranked_students rs
JOIN grade_boundaries gb ON rs.student_rank BETWEEN gb.lower_limit AND gb.upper_limit
ORDER BY rs.student_rank;

逻辑说明 #

1. 学生排名：用ROW_NUMBER()给学生按分数降序编号，确保每个学生有唯一的排名（如果允许分数相同的学生同等级，可改用RANK()或DENSE_RANK()）
2. 等级区间计算：先计算每个非0分配量等级的上下限（累计分配数量的范围），最后一个0值等级的下限为前几个等级分配总量+1，上限为学生总数
3. 关联匹配：将学生排名与等级区间关联，匹配对应的成绩等级

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