如何在SQL Server中比较同表不同行的值？学生成绩评级实现求助

一、学生成绩备注逻辑实现 #

你的原SQL存在逻辑问题：按StudentID, Marks, PassMarks分组后，会返回每个学生每门科目的单独结果，无法判断该学生是否所有科目都及格。正确的实现需要先聚合每个学生的所有科目成绩，再判断是否存在不及格情况，以下是两种常用写法：

写法1：用聚合函数判断是否存在不及格科目 #

SELECT 
    StudentID,
    CASE 
        WHEN EXISTS (SELECT 1 FROM tbl_ExamResult er2 WHERE er2.StudentID = er.StudentID AND er2.Marks < er2.PassMarks)
        THEN 'Fail'
        ELSE 'Pass'
    END AS Result
FROM tbl_ExamResult er
GROUP BY StudentID

写法2：通过标记极值简化判断 #

SELECT 
    StudentID,
    CASE 
        WHEN MIN(CASE WHEN Marks < PassMarks THEN 1 ELSE 0 END) = 0 THEN 'Pass'
        ELSE 'Fail'
    END AS Result
FROM tbl_ExamResult
GROUP BY StudentID

二、SQL Server中同表不同行比较的常用方法 #

1. 自连接（Self Join） #

适用于和同表内特定条件的行做对比，比如同一学生不同科目成绩的横向对比：

SELECT 
    er1.StudentID,
    er1.Subject AS SubjectA,
    er1.Marks AS MarksA,
    er2.Subject AS SubjectB,
    er2.Marks AS MarksB
FROM tbl_ExamResult er1
JOIN tbl_ExamResult er2 
    ON er1.StudentID = er2.StudentID 
    AND er1.Subject <> er2.Subject

2. 窗口函数（LAG/LEAD） #

用于获取当前行的上一行或下一行数据，比如查看学生科目成绩的前后变化：

SELECT 
    StudentID,
    Subject,
    Marks,
    LAG(Marks) OVER (PARTITION BY StudentID ORDER BY Subject) AS PrevSubjectMark,
    LEAD(Marks) OVER (PARTITION BY StudentID ORDER BY Subject) AS NextSubjectMark
FROM tbl_ExamResult

3. 聚合函数分组对比 #

用于对比同一分组内的统计值，比如计算每个学生的最高分与最低分差距：

SELECT 
    StudentID,
    MAX(Marks) - MIN(Marks) AS ScoreRange
FROM tbl_ExamResult
GROUP BY StudentID

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