sql - Postgresql - 通过比较组内的行进行搜索

Question

表 - grades：

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)
);

期望的结果：

查找课程 1 > 课程 2 的所有学生的 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);

---

尝试过什么

主意：

• 为一个学生创建一个 tmp 表，每行包含两个分数。
  通用汽车：student_id, score_1, score_2
• 然后从 tmp 表中查询条件score_1 > score_2。

询问：

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;

---

问题是：

• 有更好的解决方案吗？（Postgresql特别是，如果这很重要）。
• 如果查询需要比较 3 门或更多门课程，还有其他更有意义的解决方案吗？
  例如course 1 > course 2 > course 3

标签： sqlpostgresql

Answer 1

我们可以尝试使用一个简单的支点来隔离和比较各种课程的成绩：

SELECT
    student_id,
    MAX(score) FILTER (WHERE course_id = 1) AS course_1_score,
    MAX(score) FILTER (WHERE course_id = 2) AS course_2_score
FROM grades
GROUP BY
    student_id
HAVING
    MAX(score) FILTER (WHERE course_id = 1) >
    MAX(score) FILTER (WHERE course_id = 2);

您可以在该条款中添加另一个术语以HAVING比较其他课程分数。