Oracle查询需求：获取指定用户关联的最高年级学生记录

原查询通过dblink_sme跨库获取指定用户关联的学生信息，但需求调整为仅返回年级（grade_level）最高的学生详情，以下是两种可行的Oracle实现方案：

方案一：使用窗口函数（推荐） #

利用RANK()窗口函数对年级进行排序，筛选出排名第一的记录（支持多个学生同属最高年级的场景）：

SELECT student_id, first_name, last_name, school_name, grade_level
FROM (
    SELECT DISTINCT 
        cfm.student_id,
        cfm.first_name,
        cfm.last_name,
        ss.school_name,
        se.grade_level,
        RANK() OVER (ORDER BY se.grade_level DESC) AS grade_rank
    FROM ce_family_member@dblink_sme cfm
    JOIN st_enrollment@dblink_sme se
        ON se.student_id = cfm.student_id
    JOIN sy_school@dblink_sme ss
        ON ss.school_id = se.school_id
    WHERE cfm.student_id IN (
        SELECT cfm.student_id
        FROM sec_user@dblink_sme su
        JOIN ce_house_head@dblink_sme chh
            ON chh.house_head_id = su.party_id
        JOIN ce_family_to_member@dblink_sme cfm
            ON cfm.family_id = chh.family_id
        WHERE su.login_name = 'johnmccormick@homerschools'
    )
) t
WHERE grade_rank = 1
ORDER BY first_name DESC, grade_level DESC;

如果需要严格去重（避免同年级同学生的重复记录），可将RANK()替换为ROW_NUMBER()，但注意ROW_NUMBER()会给同年级学生分配不同序号，仅保留一条（按内部排序规则）。

方案二：先获取最高年级再过滤 #

先查询符合条件学生中的最高年级值，再以此为条件筛选目标记录：

SELECT DISTINCT 
    cfm.student_id,
    cfm.first_name,
    cfm.last_name,
    ss.school_name,
    se.grade_level
FROM ce_family_member@dblink_sme cfm
JOIN st_enrollment@dblink_sme se
    ON se.student_id = cfm.student_id
JOIN sy_school@dblink_sme ss
    ON ss.school_id = se.school_id
WHERE cfm.student_id IN (
    SELECT cfm.student_id
    FROM sec_user@dblink_sme su
    JOIN ce_house_head@dblink_sme chh
        ON chh.house_head_id = su.party_id
    JOIN ce_family_to_member@dblink_sme cfm
        ON cfm.family_id = chh.family_id
    WHERE su.login_name = 'johnmccormick@homerschools'
)
AND se.grade_level = (
    SELECT MAX(se_inner.grade_level)
    FROM ce_family_member@dblink_sme cfm_inner
    JOIN st_enrollment@dblink_sme se_inner
        ON se_inner.student_id = cfm_inner.student_id
    WHERE cfm_inner.student_id IN (
        SELECT cfm.student_id
        FROM sec_user@dblink_sme su
        JOIN ce_house_head@dblink_sme chh
            ON chh.house_head_id = su.party_id
        JOIN ce_family_to_member@dblink_sme cfm
            ON cfm.family_id = chh.family_id
        WHERE su.login_name = 'johnmccormick@homerschools'
    )
)
ORDER BY first_name DESC, grade_level DESC;

此方案逻辑直观，但需要重复执行子查询，数据量较大时性能略逊于窗口函数方案。

内容的提问来源于stack exchange，提问作者Vishakh .K.V