代码之家 › 专栏 › 技术社区 › Sanjay Naik

使用SQL从每个科目中提取前3名学生

oracle-database sql java

-3

Sanjay Naik · 技术社区 · 11 月前

我有两张桌子。学生和科目:

CREATE TABLE Student (
    student_id NUMBER,
    student_name VARCHAR
);

CREATE TABLE Subject (
    subject_id NUMBER,
    student_id NUMBER,
    subject_name VARCHAR,
    marks NUMBER    
);

我添加了以下数据

INSERT INTO Student
VALUES (1001, 'Sanjay Naik');
INSERT INTO Student
VALUES (1002, 'Rohit Sharma');
INSERT INTO Student
VALUES (1003, 'Virat Kolhi');
INSERT INTO Student
VALUES (1004, 'Nitish Kumar Reddy');

INSERT INTO Subject
VALUES (1,1001, 'Maths',72);
INSERT INTO Subject
VALUES (2,1001, 'English',84);
INSERT INTO Subject
VALUES (1,1002, 'Maths',79);
INSERT INTO Subject
VALUES (2,1002, 'English',94);
INSERT INTO Subject
VALUES (1,1003, 'Maths',65);
INSERT INTO Subject
VALUES (2,1003, 'English',89);
INSERT INTO Subject
VALUES (1,1004, 'Maths',54);
INSERT INTO Subject
VALUES (2,1004, 'English',51);

如何根据每门科目的成绩选出前三名学生?喜欢在英语和数学科目上都取得前三名的学生。

我试着跟随,

SELECT * FROM Student
INNER JOIN Subject
ON Student.student_id = Subject.student_id
ORDER BY Subject.marks DESC;

上述查询按照学生在两门科目中的得分顺序给出了他们的答案。如果我添加 LIMIT 3 它将获得前三名,而我想要英语和数学都是前三名的学生。

2 回复 | 直到 11 月前

samhita 11 月前

理想情况下,如果你也能分享预期的结果,那将是有益的。

你可以在每个科目中取得前三名,如下所示:

1.CTE ranked_students 将科目名称、学生姓名和分数组合在一起。 RANK() 分析函数为每个subject_name分区中的每一行分配一个排名,按标记降序排列。得分最高者排名第一。

2. rnk <= 3 根据排名过滤结果,仅包括每个科目的前3名学生。

Fiddle

WITH ranked_students AS (
    SELECT 
        su.subject_name,
        st.student_name,
        su.marks,
        RANK() OVER (PARTITION BY su.subject_name ORDER BY su.marks DESC) AS rnk
    FROM 
        Subject su
    JOIN 
        Student st ON su.student_id = st.student_id
)
SELECT 
    subject_name,
    student_name,
    marks
FROM 
    ranked_students
WHERE 
    rnk <= 3
ORDER BY 
    subject_name, rnk;

输出

主题名称	学生名	标志
英语	罗希特·夏尔马	94
英语	维拉特·科尔希	89
英语	桑杰·奈克	84
数学	罗希特·夏尔马	79
数学	桑杰·奈克	72
数学	维拉特·科尔希	65

Artem Fedorov 11 月前

使用RANK函数(或者DENSE_RANK,这取决于你需要什么样的行为,以防学生在顶部有完全相同的分数)。您可以在文档中查看有关使用RANK函数的详细信息。类似于:

WITH top_students AS (
  SELECT 
    subject_name,
    student_id,
    marks,
    RANK() OVER (PARTITION BY subject_id ORDER BY marks DESC) AS marks_rank
    FROM Subject)
    
SELECT
  subject_name,
  student_name,
  marks 
FROM top_students t
INNER JOIN Student s
ON t.student_id = s.student_id 
WHERE marks_rank < 4;