PostgreSQL中如何按数学成绩排序学生，未修数学者置于末尾

直接用单条SQL查询就能搞定，核心是通过LEFT JOIN关联学生表和数学成绩记录，配合自定义排序规则实现需求，同时原生支持LIMIT和OFFSET分页：

基础查询语句 #

SELECT s.*, COALESCE(sc.math_score, 0) AS math_score
FROM student_table s
LEFT JOIN (
    -- 筛选出所有学生的数学成绩记录
    SELECT student_id, score AS math_score
    FROM score_table
    WHERE course = 'math'
) sc ON s.id = sc.student_id
-- 排序规则：有数学成绩的排前面，再按成绩降序；无成绩的统一排末尾
ORDER BY 
    CASE WHEN sc.math_score IS NOT NULL THEN 0 ELSE 1 END,
    sc.math_score DESC
-- 替换为实际的分页参数：每页条数和偏移量
LIMIT 10 OFFSET 20;

针对多数学成绩记录的调整 #

如果同一个学生有多条数学成绩（比如补考、多次测验），可以在子查询里聚合成绩，比如取最高分：

SELECT s.*, COALESCE(sc.math_score, 0) AS math_score
FROM student_table s
LEFT JOIN (
    SELECT student_id, MAX(score) AS math_score
    FROM score_table
    WHERE course = 'math'
    GROUP BY student_id
) sc ON s.id = sc.student_id
ORDER BY 
    CASE WHEN sc.math_score IS NOT NULL THEN 0 ELSE 1 END,
    sc.math_score DESC
LIMIT 10 OFFSET 20;

方案优势 #

• 单查询完成所有逻辑，无需拆分后合并，天然支持分页参数，解决原思路的分页难题
• LEFT JOIN确保所有学生都被返回，不会遗漏未修数学的学生
• 排序逻辑清晰，通过CASE表达式优先区分有/无成绩的分组，再按成绩降序排列，完全符合需求
• 性能可控：给student_table.id和score_table.student_id建立索引后，关联查询的效率能得到保证

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