PostgreSQL分组跨行查询：筛选课1分>课2的学生ID及多课程对比优化

嘿，针对你提出的两个问题，我来分享几个更高效、易维护的PostgreSQL专属解决方案，尤其是处理多课程对比的场景：

前置背景 #

表结构 #

CREATE TABLE grades (
    student_id bigserial NOT NULL,
    course_id bigserial NOT NULL,
    score int NOT NULL,
    created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    UNIQUE (student_id, course_id)
);

示例数据 #

INSERT INTO grades(student_id, course_id, score) VALUES 
(1, 1, 60), (1, 2, 70), (1, 3, 65), 
(2, 1, 70), (2, 2, 60), (2, 3, 80), 
(3, 1, 90), (3, 2, 90), (3, 3, 85);

已尝试的方案 #

SELECT * FROM (
    SELECT grades_1.student_id AS sid, grades_1.score AS score_1, grades_2.score AS score_2
    FROM (SELECT student_id, score FROM grades WHERE course_id = 1 ORDER BY student_id) AS grades_1
    INNER JOIN (SELECT student_id, score FROM grades WHERE course_id = 2 ORDER BY student_id) AS grades_2
    ON grades_1.student_id = grades_2.student_id
) AS gm WHERE gm.score_1 > gm.score_2;

问题1：更优的PostgreSQL专属解决方案 #

你的原方案可行，但存在冗余（子查询里的ORDER BY毫无意义，还会增加额外开销），这里推荐两个更高效的方案：

方案1：PostgreSQL原生FILTER条件聚合 #

利用PostgreSQL专属的FILTER语法，一次扫描表就完成行转列和条件判断，逻辑清晰且性能更优：

SELECT student_id
FROM grades
WHERE course_id IN (1, 2) -- 只扫描需要的课程数据，减少处理量
GROUP BY student_id
HAVING MAX(score) FILTER (WHERE course_id = 1) > MAX(score) FILTER (WHERE course_id = 2);

优势：

• 仅需扫描一次grades表，比原方案的两次子查询+连接更高效
• FILTER语法比通用的CASE WHEN更直观，是PostgreSQL的原生优化语法
• 利用表上的UNIQUE (student_id, course_id)约束，MAX(score)等价于直接取对应课程的分数（每个学生每门课只有一条记录）

方案2：简化自连接 #

去掉原方案中无用的ORDER BY，简化自连接逻辑，PostgreSQL的查询优化器会更好地利用索引：

SELECT g1.student_id
FROM grades g1
JOIN grades g2 ON g1.student_id = g2.student_id
WHERE g1.course_id = 1 
  AND g2.course_id = 2
  AND g1.score > g2.score;

优势：

• 语法简洁，可读性强
• 避免了子查询嵌套的额外开销，优化器可以直接利用(student_id, course_id)的唯一索引快速定位数据

问题2：对比3门及以上课程的合理方案 #

当需要对比3门及更多课程时，条件聚合的优势会被无限放大——不需要多次自连接（否则课程越多，连接逻辑越复杂、性能越差），只需要在HAVING子句中添加对应的判断条件即可：

示例：课程1分数>课程2分数>课程3分数 #

SELECT student_id
FROM grades
WHERE course_id IN (1, 2, 3)
GROUP BY student_id
HAVING MAX(score) FILTER (WHERE course_id = 1) > MAX(score) FILTER (WHERE course_id = 2)
   AND MAX(score) FILTER (WHERE course_id = 2) > MAX(score) FILTER (WHERE course_id = 3);

说明：

• 自动排除了缺少任意一门课程成绩的学生（因为MAX(score) FILTER会返回NULL，NULL参与比较时结果为NULL，不会被HAVING条件选中）
• 无论需要对比多少门课程，只需要一次表扫描，逻辑扩展非常方便（比如再加课程4，只需在WHERE和HAVING里各加一行）
• 如果需要允许学生缺少部分课程成绩（只对比已有的课程），可以用COALESCE给缺失成绩设置默认值，比如：

SELECT student_id
FROM grades
GROUP BY student_id
HAVING COALESCE(MAX(score) FILTER (WHERE course_id = 1), 0) > COALESCE(MAX(score) FILTER (WHERE course_id = 2), 0)
   AND COALESCE(MAX(score) FILTER (WHERE course_id = 2), 0) > COALESCE(MAX(score) FILTER (WHERE course_id = 3), 0);

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