sql - 在 oracle 中仅显示 SQL 查询的最大结果

Question

我有一个查询：

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

我只想要总学分最高的学生。为此，它将是 ID 为 JO100 的学生。如何在查询中实现这一点？

标签： sqloracle

Answer 1

尝试这个 -

SELECT
    *
FROM
    (
        SELECT
            A.*,
            DENSE_RANK() OVER(
                ORDER BY "TOTAL CREDITS" DESC
            ) AS RNK
        FROM
            (
                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
            ) A
    )
WHERE
    RNK   = 1;