代码之家 › 专栏 › 技术社区 › JagaSrik

找出在每个学期和每门课程中得分最高的学生的名字?

oracle-sqldeveloper oracle sql

JagaSrik · 技术社区 · 6 年前

有两个表,即students和markscard,我为每个表编写了create-able脚本和insert语句中的一些示例数据。

我觉得这里缺少了一些链接,无法得到在每门sem和每门课程中得分最高的学生的名字,是我错了还是缺少了什么?提前谢谢。

表的sql脚本

CREATE TABLE student (
    name     VARCHAR2(100),
    regno    NUMBER(20) PRIMARY KEY,
    dob      DATE,
    course   VARCHAR2(200)
);

INSERT INTO student VALUES (
    'S1',
    1001,
    '5-04-2000',
    'computer'
);

INSERT INTO student VALUES (
    'S2',
    1002,
    '8-04-2010',
    'Electronics'
);

INSERT INTO student VALUES (
    'S3',
    1003,
    '2-04-2100',
    'management'
);

INSERT INTO student VALUES (
    'S4',
    1004,
    '28-05-2000',
    'Electronics'
);

INSERT INTO student VALUES (
    'S5',
    1005,
    '2-04-2000',
    'computer'
);

COMMIT;

CREATE TABLE markscard (
    regno    NUMBER(20)
        REFERENCES student ( regno ),
    sem      VARCHAR2(100),
    sub1     NUMBER(20),
    sub2     NUMBER(20),
    sub3     NUMBER(20),
    tot      NUMBER(20),
    avge     NUMBER(20),
    result   VARCHAR2(200)
);

INSERT INTO markscard VALUES (
    1001,
    1,
    30,
    30,
    30,
    90,
    30,
    'pass'
);

INSERT INTO markscard VALUES (
    1002,
    1,
    10,
    10,
    10,
    100,
    10,
    'fail'
);

INSERT INTO markscard VALUES (
    1003,
    2,
    100,
    100,
    100,
    300,
    100,
    'distinction'
);

INSERT INTO markscard VALUES (
    1004,
    2,
    20,
    20,
    20,
    60,
    20,
    'pass'
);

INSERT INTO markscard VALUES (
    1005,
    1,
    30,
    30,
    30,
    100,
    30,
    'pass'
);

COMMIT;

这是我尝试过的查询,但无法成功获得学生姓名,因为我觉得有一些链接丢失。

SELECT
    MAX(hightest_score),
    course_name,
    sem
FROM
    (
        SELECT
            MAX(markscard.tot) AS hightest_score,
            student.course   AS course_name,
            markscard.sem    AS sem,
            student.name     AS sname
        FROM
            markscard,
            student
        WHERE
            student.regno = markscard.regno
        GROUP BY
            student.course,
            markscard.sem,
            student.name
    )
GROUP BY
    course_name,
    sem;

2 回复 | 直到 6 年前

thatjeffsmith 6 年前

首先,学会使用恰当的,明确的, 标准 JOIN 语法。其次,最好的方法是使用窗口函数:

SELECT sm.*
FROM (SELECT s.course as course_name, mc.sem, s.name, mc.tot,
              MAX(tot) OVER (PARTITION BY s.course, mc.sem) as max_tot
      FROM markscard mc JOIN
           student s
           ON s.regno = mc.regno
     ) sm
WHERE tot = max_tot;

David Blyth 6 年前

我喜欢这个选项。。。

WITH RANKED_DATA
     AS (SELECT S.REGNO,
                S.NAME,
                S.COURSE,
                M.SEM,
                M.TOT,
                RANK () OVER (PARTITION BY S.COURSE, M.SEM ORDER BY M.TOT DESC) AS RK
           FROM MARKSCARD M INNER JOIN STUDENT S ON (M.REGNO = S.REGNO))
SELECT *
  FROM RANKED_DATA
 WHERE RK = 1;