感谢 老虎刘 刘老师 对 5月20日 SQL 问题纠正贴 ---PostgreSQL 同一种SQL为什么这样写会提升45%性能

sql_test=# WITH LowestEnglishScoreStudents AS (
    SELECT DISTINCT ON (student_id)
        student_id
    FROM
        scores
    WHERE
        subject_id = 2
    ORDER BY
        student_id, score ASC
    LIMIT 2
),
MathScoresForLowestEnglish AS (
    SELECT
        s.student_id,
        st.name,
        s.score AS math_score
    FROM
        scores s
    JOIN
        LowestEnglishScoreStudents les ON s.student_id = les.student_id
    JOIN
        students st ON s.student_id = st.student_id
    WHERE
        s.subject_id = 1
)
SELECT
    student_id,
    name,
    math_score
FROM
    MathScoresForLowestEnglish
ORDER BY
    math_score DESC;
 student_id | name  | math_score 
------------+-------+------------
          1 | John  |         95
          2 | Alice |         92
          1 | John  |         90
          2 | Alice |         80
(4 rows)