如何在PL/pgSQL中查询学生每行的科目最高分及对应科目

现有学生成绩表如下：

需要查询每个学生的最高得分及对应的科目，期望输出如下：

方法1：利用LATERAL子查询与UNNEST列转行 #

该方法先将每个学生的科目和成绩转换为行数据，再筛选出每个学生的最高分记录。

CREATE OR REPLACE FUNCTION get_student_max_scores()
RETURNS TABLE(student_id INT, subject_name TEXT, max_mark INT) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        ss.student_id,
        unnest(array['Subject A', 'Subject B', 'Subject C', 'Subject D']) AS subject_name,
        unnest(array[ss.subject_a, ss.subject_b, ss.subject_c, ss.subject_d]) AS mark
    FROM student_scores ss
    LATERAL (
        SELECT MAX(mark) AS max_mark
        FROM unnest(array[ss.subject_a, ss.subject_b, ss.subject_c, ss.subject_d]) AS mark
    ) AS sub
    WHERE unnest(array[ss.subject_a, ss.subject_b, ss.subject_c, ss.subject_d]) = sub.max_mark;
END;
$$ LANGUAGE plpgsql;

调用函数获取结果：

SELECT * FROM get_student_max_scores();

方法2：使用ROW_NUMBER()窗口函数 #

先通过UNION ALL完成列转行，再用窗口函数对每个学生的成绩排序，取排名第一的记录。

CREATE OR REPLACE FUNCTION get_student_max_scores()
RETURNS TABLE(student_id INT, subject_name TEXT, max_mark INT) AS $$
BEGIN
    RETURN QUERY
    WITH subject_scores AS (
        SELECT student_id, 'Subject A' AS subject_name, subject_a AS mark FROM student_scores
        UNION ALL
        SELECT student_id, 'Subject B' AS subject_name, subject_b AS mark FROM student_scores
        UNION ALL
        SELECT student_id, 'Subject C' AS subject_name, subject_c AS mark FROM student_scores
        UNION ALL
        SELECT student_id, 'Subject D' AS subject_name, subject_d AS mark FROM student_scores
    ),
    ranked_scores AS (
        SELECT 
            student_id,
            subject_name,
            mark,
            ROW_NUMBER() OVER(PARTITION BY student_id ORDER BY mark DESC) AS rank
        FROM subject_scores
    )
    SELECT student_id, subject_name, mark AS max_mark
    FROM ranked_scores
    WHERE rank = 1;
END;
$$ LANGUAGE plpgsql;

补充说明 #

• 若学生存在多个科目同分且均为最高分的情况：方法1会返回所有符合条件的科目；方法2默认仅返回其中一个，若需返回全部，可将ROW_NUMBER()替换为RANK()。
• 假设原表的创建语句为：

CREATE TABLE student_scores (
    student_id INT PRIMARY KEY,
    subject_a INT,
    subject_b INT,
    subject_c INT,
    subject_d INT
);

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