查询每个班级中总分前3的学生信息

示例

班级表

t_class

学生表

t_student

建表语句

-- 创建班级表
CREATE TABLE t_class (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- 创建学生表
CREATE TABLE t_student (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    score INT,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES t_class(id)
);

-- 模拟数据
-- 插入班级数据
INSERT INTO t_class (id, name) VALUES
(1, 'Class A'),
(2, 'Class B'),
(3, 'Class C');

-- 插入学生数据
INSERT INTO t_student (id, name, score, class_id) VALUES
-- Class A
(1, 'Alice', 85, 1),
(2, 'Bob', 90, 1),
(3, 'Charlie', 78, 1),
(4, 'David', 92, 1),
(5, 'Eva', 88, 1),
(6, 'Frank', 80, 1),
(7, 'Grace', 87, 1),
(8, 'Hank', 91, 1),
(9, 'Ivy', 89, 1),
(10, 'Jack', 84, 1),
-- Class B
(11, 'Kim', 86, 2),
(12, 'Leo', 81, 2),
(13, 'Mia', 93, 2),
(14, 'Nina', 79, 2),
(15, 'Oscar', 90, 2),
(16, 'Paul', 82, 2),
(17, 'Quincy', 89, 2),
(18, 'Rachel', 91, 2),
(19, 'Steve', 88, 2),
(20, 'Tina', 85, 2),
-- Class C
(21, 'Uma', 87, 3),
(22, 'Victor', 92, 3),
(23, 'Wendy', 81, 3),
(24, 'Xander', 89, 3),
(25, 'Yara', 90, 3),
(26, 'Zack', 84, 3),
(27, 'Abby', 88, 3),
(28, 'Brian', 80, 3),
(29, 'Cindy', 86, 3),
(30, 'Derek', 82, 3);

SQL

SELECT
    t3.NAME class_name,
    t1.NAME student_name,
    t1.score 
FROM
    (
    SELECT
        s.ID,
        s.NAME,
        s.score,
        s.class_id,
        -- 根据班级分组然后再根据分数降序排列得到分组后的序号
        ROW_NUMBER ( ) OVER ( PARTITION BY s.class_id ORDER BY s.score DESC ) AS RANK 
    FROM
        t_student s 
    ) t1
    JOIN t_student t2 ON t1.ID = t2.ID 
    AND t1.RANK <= 3
    JOIN t_class t3 ON t1.class_id = t3.ID 
ORDER BY
    t3.NAME,
    t1.score DESC,
    t1.NAME

结果展示