求助：如何编写SQL查询识别学生成绩下降2档及以上的情况

要实现这个需求，核心是给字母等级映射数值，并获取每个学生最近两次的考试成绩来对比差距，具体步骤如下：

1. 字母等级转数值 #

根据你定义的规则（G=1，A=7），用CASE WHEN语句将字母等级转换为可计算的数值：

CASE Grade
    WHEN 'A' THEN 7
    WHEN 'B' THEN 6
    WHEN 'C' THEN 5
    WHEN 'D' THEN 4
    WHEN 'E' THEN 3
    WHEN 'F' THEN 2
    WHEN 'G' THEN 1
END AS grade_score

2. 获取每个学生的最近两次考试记录 #

使用窗口函数ROW_NUMBER()，按StudentID分组，对每个学生的考试记录按ExamDate倒序排序，标记出最新（排名1）和上一次（排名2）的考试：

WITH ranked_exams AS (
    SELECT
        Exam,
        StudentID,
        ExamDate,
        Grade,
        ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY ExamDate DESC) AS exam_rank,
        CASE Grade
            WHEN 'A' THEN 7
            WHEN 'B' THEN 6
            WHEN 'C' THEN 5
            WHEN 'D' THEN 4
            WHEN 'E' THEN 3
            WHEN 'F' THEN 2
            WHEN 'G' THEN 1
        END AS grade_score
    FROM your_table_name
)

3. 对比成绩差距并筛选符合条件的记录 #

将最新和上一次的考试记录关联，计算成绩数值的差值，筛选出最新成绩数值 - 上一次成绩数值 ≤ -2的情况（即成绩下降2档及以上）：

SELECT
    latest.Exam || '/' || latest.StudentID AS result
FROM ranked_exams latest
JOIN ranked_exams prev
    ON latest.StudentID = prev.StudentID
    AND latest.exam_rank = 1
    AND prev.exam_rank = 2
WHERE latest.grade_score - prev.grade_score <= -2;

完整SQL代码 #

WITH ranked_exams AS (
    SELECT
        Exam,
        StudentID,
        ExamDate,
        Grade,
        ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY ExamDate DESC) AS exam_rank,
        CASE Grade
            WHEN 'A' THEN 7
            WHEN 'B' THEN 6
            WHEN 'C' THEN 5
            WHEN 'D' THEN 4
            WHEN 'E' THEN 3
            WHEN 'F' THEN 2
            WHEN 'G' THEN 1
        END AS grade_score
    FROM your_table_name
)
SELECT
    latest.Exam || '/' || latest.StudentID AS result
FROM ranked_exams latest
INNER JOIN ranked_exams prev
    ON latest.StudentID = prev.StudentID
    AND latest.exam_rank = 1
    AND prev.exam_rank = 2
WHERE latest.grade_score - prev.grade_score <= -2;

结果验证 #

针对你的示例数据，运行上述代码后会返回：

Phy01b/AA009
Bio1b/AA004

完全符合预期：

• AA009：上一次成绩B(6)，最新成绩E(3)，3-6=-3 ≤ -2，符合条件
• AA004：上一次成绩C(5)，最新成绩F(2)，2-5=-3 ≤ -2，符合条件

内容的提问来源于stack exchange，提问作者my-socrates-note