学生总分SQL查询优化及替代实现方案咨询

需求说明 #

针对学生成绩数据集，需实现以下查询逻辑：

• 按总分降序展示学生总分
• 同一科目多次考试取最高分计入总分
• 若任一科目最高分低于30，则排除该学生

现有SQL查询可得到正确结果，现寻求优化方案或其他实现方式。

数据库表结构 #

CREATE TABLE Student (
  id INTEGER PRIMARY KEY auto_increment,
  name TEXT NOT NULL,
  subject TEXT NOT NULL,
  marks int 
);

测试数据插入 #

INSERT INTO Student (name, subject, marks) VALUES ('Rahul', 'Math', 60);
INSERT INTO Student (name, subject, marks) VALUES ('Rahul', 'Science', 60);
INSERT INTO Student (name, subject, marks) VALUES ('Rahul', 'English', 29);
INSERT INTO Student (name, subject, marks) VALUES ('Rahul', 'English', 37);

INSERT INTO Student (name, subject, marks) VALUES ('Nitin', 'Science', 68);
INSERT INTO Student (name, subject, marks) VALUES ('Nitin', 'English', 69);
INSERT INTO Student (name, subject, marks) VALUES ('Nitin', 'Math', 73);

INSERT INTO Student (name, subject, marks) VALUES ('Naveen', 'Math', 60);
INSERT INTO Student (name, subject, marks) VALUES ('Naveen', 'Science', 20);
INSERT INTO Student (name, subject, marks) VALUES ('Naveen', 'English', 29);
INSERT INTO Student (name, subject, marks) VALUES ('Naveen', 'English', 87);

原查询语句 #

SELECT name, sum(score) as totalScore from (
  SELECT name, subject, max(marks) as score from Student where name not in (
    SELECT name from (
      SELECT name, subject, max(marks) as marks from Student group by name, subject
    ) as abc where marks < 30
  ) group by name, subject
) as f group by name order by totalScore DESC;

原查询输出 #

name    totalScore
Nitin   210
Rahul   157

优化方案与替代实现 #

原查询嵌套多层子查询，存在重复计算（多次对Student表按name,subject分组取最高分），以下是更简洁高效的实现方式：

方案一：使用HAVING子句筛选（推荐）

先分组计算每个学生各科的最高分，再通过HAVING子句确保所有科目最高分不低于30，最后直接求和排序，仅需一次分组计算，逻辑清晰且效率更高：

SELECT 
    name,
    SUM(max_marks) AS totalScore
FROM (
    SELECT 
        name,
        subject,
        MAX(marks) AS max_marks
    FROM Student
    GROUP BY name, subject
) AS subject_max
GROUP BY name
HAVING MIN(max_marks) >= 30
ORDER BY totalScore DESC;

方案二：使用窗口函数（适用于MySQL 8.0+、PostgreSQL等支持窗口函数的数据库）

通过窗口函数同时计算学生各科最高分及自身所有科目最高分的最小值，直接筛选符合条件的学生并求和，适合复杂多维度统计场景：

SELECT 
    name,
    SUM(max_marks) AS totalScore
FROM (
    SELECT 
        name,
        subject,
        MAX(marks) OVER (PARTITION BY name, subject) AS max_marks,
        MIN(MAX(marks) OVER (PARTITION BY name, subject)) OVER (PARTITION BY name) AS min_subject_max
    FROM Student
    GROUP BY name, subject, marks
) AS temp
WHERE min_subject_max >= 30
GROUP BY name
ORDER BY totalScore DESC;

方案三：使用JOIN替代NOT IN

通过左连接排除存在不合格科目的学生，避免NOT IN可能带来的性能瓶颈，在大数据量场景下优化器可能生成更优执行计划：

SELECT 
    s.name,
    SUM(MAX(s.marks)) AS totalScore
FROM Student s
LEFT JOIN (
    SELECT name 
    FROM (
        SELECT name, subject, MAX(marks) AS max_marks 
        FROM Student 
        GROUP BY name, subject
    ) AS sub 
    WHERE max_marks < 30
) AS exclude ON s.name = exclude.name
WHERE exclude.name IS NULL
GROUP BY s.name
ORDER BY totalScore DESC;

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