sql - 如何选择TOP 5但排除一些结果(SQL Server)

Question

所以我有这段代码..

SELECT TOP 5 Student_ID,
CASE
    WHEN SUM(Grade)/5 >= 96.5 THEN '4.0'
    WHEN SUM(Grade)/5 > 92.4 THEN '3.5'
    WHEN SUM(Grade)/5 > 88.4 THEN '3.0'
    WHEN SUM(Grade)/5 > 84.4 THEN '2.5'
    WHEN SUM(Grade)/5 > 79.4 THEN '2.0'
    WHEN SUM(Grade)/5 > 74.4 THEN '1.5'
    WHEN SUM(Grade)/5 > 69.4 THEN '1.0'
    WHEN SUM(Grade)/5 < 69.5 THEN 'R'
ELSE ''
END AS 'GPA'
FROM Grades
GROUP BY Student_ID
ORDER BY GPA DESC

这是结果:

Student_ID | GPA   
 200128       R
 200122      3.5
 200126      3.5
 200120      3.0
 200125      3.0

如何在获得 TOP 5 时排除 GPA 为“R”的学生？

Answer 1

更改顺序:

ORDER BY (case when GPA = 'R' then 2 else 1 end),
         GPA DESC

你也可以这样做:

ORDER BY SUM(Grade)/5 DESC

这略有不同，因为第一种方式会平等对待所有具有相同“GPA”的学生。这将按数值对它们(在幕后)进行排序。