MySQL如何获取学生得F后的下一次考试成绩？

问题分析 #

你需要筛选出学生取得F等级的记录，并关联该学生后续第一次考试的成绩。原查询存在两个关键问题：

1. 字符串类型的成绩值未加单引号，grade = F会被MySQL误判为列名，正确写法应为grade = 'F'
2. 未实现获取后续考试成绩的逻辑，只是重复了当前成绩字段

解决方案（MySQL 8.0+ 推荐） #

使用窗口函数LEAD()可以简洁地获取同一分组（学生ID）下的下一条记录数据，配合日期排序即可得到目标结果：

SELECT id, grade, next_grade
FROM (
    SELECT 
        id,
        grade,
        -- 按学生分组，按考试日期排序，获取下一次考试的成绩
        LEAD(grade) OVER (PARTITION BY id ORDER BY STR_TO_DATE(date, '%m/%d/%y')) AS next_grade
    FROM test_scores
) AS ranked_scores
-- 筛选出当前成绩为F的记录
WHERE grade = 'F';

代码说明 #

• STR_TO_DATE(date, '%m/%d/%y')：将字符串格式的日期转换为日期类型，确保排序逻辑正确（避免字符串排序的错误，比如'12/1/22'和'11/1/22'的字符串排序结果与实际日期顺序不符）
• PARTITION BY id：按学生ID分组，确保只在同一学生的考试记录中查找下一次成绩
• LEAD(grade)：获取分组内当前记录的下一条记录的grade值

兼容MySQL 5.x的解决方案（无窗口函数） #

如果你的MySQL版本不支持窗口函数，可以使用自连接+子查询的方式实现：

SELECT 
    ts1.id,
    ts1.grade,
    ts2.grade AS next_grade
FROM test_scores ts1
JOIN test_scores ts2 ON ts1.id = ts2.id
WHERE ts1.grade = 'F'
  -- 关联当前学生在F考试之后的最早一次考试记录
  AND STR_TO_DATE(ts2.date, '%m/%d/%y') = (
      SELECT MIN(STR_TO_DATE(date, '%m/%d/%y'))
      FROM test_scores
      WHERE id = ts1.id AND STR_TO_DATE(date, '%m/%d/%y') > STR_TO_DATE(ts1.date, '%m/%d/%y')
  );

代码说明 #

• 子查询用于找到当前学生在F等级考试之后的最早考试日期
• 通过自连接将该日期对应的成绩关联到F等级的记录上

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