Oracle SQL查询：仅展示总学分最高的学生记录

嗨，这里有几种实用的方法帮你实现只显示总学分最高的学生记录，你可以根据自己使用的数据库类型和需求选择最合适的方案：

问题回顾 #

你当前的查询语句是：

SELECT STUDENT.S_ID AS "ID", 
       STUDENT.S_LAST ||' '|| STUDENT.S_FIRST AS "Student Name", 
       COUNT(COURSE.COURSE_NO) AS "Number of courses", 
       SUM(COURSE.CREDITS) AS "Total Credits" 
FROM STUDENT 
JOIN ENROLLMENT ON ENROLLMENT.S_ID = STUDENT.S_ID 
JOIN COURSE_SECTION ON COURSE_SECTION.C_SEC_ID = ENROLLMENT.C_SEC_ID 
JOIN COURSE ON COURSE.COURSE_NO = COURSE_SECTION.COURSE_NO 
GROUP BY STUDENT.S_ID, STUDENT.S_LAST, STUDENT.S_FIRST;

返回的结果如下：

ID    Student Name        Number of courses  Total Credits
------ ------------------- ----------------- -------------
JO100  Jones Tammy        6                  21
MA100  Marsh John         5                  15
SM100  Smith Mike         2                  6
PE100  Perez Jorge        6                  18
JO101  Johnson Lisa       5                  15
NG100  Nguyen Ni          4                  12

你需要修改查询，只保留总学分最高的那条（也就是JO100的记录），如果有多个学生学分并列最高，也可以选择是否显示所有并列记录。

方案1：用窗口函数（推荐，适合现代数据库） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server、Oracle等），ROW_NUMBER()是最简洁的实现方式。我们可以先把原查询的结果存为一个临时数据集（CTE），然后给每条记录按总学分降序分配行号，最后只取行号为1的记录：

WITH StudentCourseStats AS (
    SELECT STUDENT.S_ID AS "ID", 
           STUDENT.S_LAST ||' '|| STUDENT.S_FIRST AS "Student Name", 
           COUNT(COURSE.COURSE_NO) AS "Number of courses", 
           SUM(COURSE.CREDITS) AS "Total Credits" 
    FROM STUDENT 
    JOIN ENROLLMENT ON ENROLLMENT.S_ID = STUDENT.S_ID 
    JOIN COURSE_SECTION ON COURSE_SECTION.C_SEC_ID = ENROLLMENT.C_SEC_ID 
    JOIN COURSE ON COURSE.COURSE_NO = COURSE_SECTION.COURSE_NO 
    GROUP BY STUDENT.S_ID, STUDENT.S_LAST, STUDENT.S_FIRST
)
SELECT *
FROM StudentCourseStats
WHERE ROW_NUMBER() OVER (ORDER BY "Total Credits" DESC) = 1;

💡 小提示：如果想返回所有并列最高学分的学生，把ROW_NUMBER()换成RANK()或者DENSE_RANK()就行——RANK()会跳过并列后的行号，DENSE_RANK()不会，根据需求选。

方案2：用子查询筛选最大值（兼容性拉满） #

如果你的数据库不支持窗口函数（比如老版本MySQL），可以先通过子查询算出最高的总学分，再筛选出总学分等于这个值的记录：

SELECT STUDENT.S_ID AS "ID", 
       STUDENT.S_LAST ||' '|| STUDENT.S_FIRST AS "Student Name", 
       COUNT(COURSE.COURSE_NO) AS "Number of courses", 
       SUM(COURSE.CREDITS) AS "Total Credits" 
FROM STUDENT 
JOIN ENROLLMENT ON ENROLLMENT.S_ID = STUDENT.S_ID 
JOIN COURSE_SECTION ON COURSE_SECTION.C_SEC_ID = ENROLLMENT.C_SEC_ID 
JOIN COURSE ON COURSE.COURSE_NO = COURSE_SECTION.COURSE_NO 
GROUP BY STUDENT.S_ID, STUDENT.S_LAST, STUDENT.S_FIRST
HAVING SUM(COURSE.CREDITS) = (
    SELECT MAX(TotalCredits)
    FROM (
        SELECT SUM(COURSE.CREDITS) AS TotalCredits
        FROM STUDENT 
        JOIN ENROLLMENT ON ENROLLMENT.S_ID = STUDENT.S_ID 
        JOIN COURSE_SECTION ON COURSE_SECTION.C_SEC_ID = ENROLLMENT.C_SEC_ID 
        JOIN COURSE ON COURSE.COURSE_NO = COURSE_SECTION.COURSE_NO 
        GROUP BY STUDENT.S_ID, STUDENT.S_LAST, STUDENT.S_FIRST
    ) AS SubQuery
);

这个方法会自动返回所有总学分等于最高值的学生，不需要额外调整。

方案3：针对特定数据库的快捷写法 #

• SQL Server：可以用TOP 1 WITH TIES直接返回所有并列最高的记录：

  SELECT TOP 1 WITH TIES
         STUDENT.S_ID AS "ID", 
         STUDENT.S_LAST ||' '|| STUDENT.S_FIRST AS "Student Name", 
         COUNT(COURSE.COURSE_NO) AS "Number of courses", 
         SUM(COURSE.CREDITS) AS "Total Credits" 
  FROM STUDENT 
  JOIN ENROLLMENT ON ENROLLMENT.S_ID = STUDENT.S_ID 
  JOIN COURSE_SECTION ON COURSE_SECTION.C_SEC_ID = ENROLLMENT.C_SEC_ID 
  JOIN COURSE ON COURSE.COURSE_NO = COURSE_SECTION.COURSE_NO 
  GROUP BY STUDENT.S_ID, STUDENT.S_LAST, STUDENT.S_FIRST
  ORDER BY "Total Credits" DESC;
  

• MySQL/PostgreSQL：如果只需要一条记录，直接在原查询末尾加ORDER BY "Total Credits" DESC LIMIT 1就行，但这种方法不会返回并列记录，适合确定只有一个最高值的场景。

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