如何按自定义优先级PhD>MA>CERT提取学生最高项目等级？

嘿，我明白你现在的需求——要根据PhD>MA>CERT的优先级，给每个学生找出最高等级的项目。之前用自定义排名取最小值的方法可能因为硬编码不够灵活或者排序逻辑没处理好出问题，我给你分享几个更可靠的方案：

方案一：通用窗口函数法（适用于大多数SQL数据库） #

这个方法用ROW_NUMBER()窗口函数，先给每个项目按优先级分配排序权重，再按学生分组取优先级最高的项目。如果担心项目前缀（比如A.、B.）变化，我们可以提取项目后缀来判断优先级，这样更通用：

WITH ranked_student_programs AS (
    SELECT 
        Student,
        Program,
        ROW_NUMBER() OVER (
            PARTITION BY Student 
            ORDER BY 
                -- 提取项目后缀判断优先级，适配任意前缀的项目
                CASE SUBSTRING_INDEX(Program, '.', -1)
                    WHEN 'PhD' THEN 1
                    WHEN 'MA' THEN 2
                    WHEN 'CERT' THEN 3
                    ELSE 4 -- 处理未知项目类型，放在最后
                END ASC
        ) AS priority_rank
    FROM 表A
)
SELECT 
    Student,
    Program AS `Highest Program`
FROM ranked_student_programs
WHERE priority_rank = 1;

为什么这个方案更好？ #

• 不管项目前缀是A、B还是其他，只要后缀是PhD/MA/CERT，就能正确识别优先级，避免硬编码所有可能的项目名
• 窗口函数逻辑清晰，容易理解和维护，出问题也方便排查

方案二：PostgreSQL专属简洁写法 #

如果你用的是PostgreSQL，DISTINCT ON语法可以一步到位，代码更简洁：

SELECT DISTINCT ON (Student)
    Student,
    Program AS `Highest Program`
FROM 表A
ORDER BY 
    Student,
    CASE SUBSTRING_INDEX(Program, '.', -1)
        WHEN 'PhD' THEN 1
        WHEN 'MA' THEN 2
        WHEN 'CERT' THEN 3
        ELSE 4
    END ASC;

方案三：Oracle专属聚合函数法 #

Oracle用户可以用KEEP DENSE_RANK FIRST来直接获取最高优先级的项目：

SELECT 
    Student,
    MAX(Program) KEEP (
        DENSE_RANK FIRST ORDER BY 
            CASE SUBSTRING_INDEX(Program, '.', -1)
                WHEN 'PhD' THEN 1
                WHEN 'MA' THEN 2
                WHEN 'CERT' THEN 3
                ELSE 4
            END ASC
    ) AS "Highest Program"
FROM 表A
GROUP BY Student;

关于你之前方法的可能问题 #

你提到的“自定义排名后取最小值”如果出问题，大概率是因为直接用整个项目名来分配排名（比如把'A.PhD'设为1，'B.MA'设为2），但如果后续出现'C.PhD'这类新的项目，排名逻辑就会失效。而提取后缀的方式就能避免这个问题，适配更多场景。

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