SQL多对多关系查询：如何将各班前3名最年长学生展示在同一输出行

要实现将每个班级最年长的前3名学生信息展示在同一行，核心思路是先给每个班级的学生按年龄排名，再通过条件聚合将多行结果转为单行。以下是具体实现步骤：

1. 给班级内学生按年龄排名 #

首先关联三张表，用窗口函数给每个班级的学生按年龄降序生成排名：

SELECT
    c.class_id,
    c.class_name,
    s.first_name,
    s.age,
    -- 按班级分组，年龄降序排名，年龄相同则按默认顺序分配唯一排名
    ROW_NUMBER() OVER (PARTITION BY c.class_id ORDER BY s.age DESC) AS rank_num
FROM classes c
JOIN student_classes sc ON c.class_id = sc.class_id
JOIN student s ON sc.student_id = s.student_id

• 如果需要处理年龄并列的情况（比如两个学生同属班级最大年龄，都算Top1），可以把ROW_NUMBER()换成RANK()，但此时同一个班级会有多个rank_num=1的行，后续聚合时只会取其中一个（取决于数据库的排序规则）。

2. 条件聚合转成单行结果 #

基于上面的排名子查询，用CASE语句筛选出排名1、2、3的学生信息，通过聚合函数将多行转为单行：

SELECT
    class_id,
    class_name,
    -- 提取排名第1的学生姓名和年龄
    MAX(CASE WHEN rank_num = 1 THEN first_name END) AS top1_first_name,
    MAX(CASE WHEN rank_num = 1 THEN age END) AS top1_age,
    -- 提取排名第2的学生姓名和年龄
    MAX(CASE WHEN rank_num = 2 THEN first_name END) AS top2_first_name,
    MAX(CASE WHEN rank_num = 2 THEN age END) AS top2_age,
    -- 提取排名第3的学生姓名和年龄
    MAX(CASE WHEN rank_num = 3 THEN first_name END) AS top3_first_name,
    MAX(CASE WHEN rank_num = 3 THEN age END) AS top3_age
FROM (
    SELECT
        c.class_id,
        c.class_name,
        s.first_name,
        s.age,
        ROW_NUMBER() OVER (PARTITION BY c.class_id ORDER BY s.age DESC) AS rank_num
    FROM classes c
    JOIN student_classes sc ON c.class_id = sc.class_id
    JOIN student s ON sc.student_id = s.student_id
) ranked_students
GROUP BY class_id, class_name
ORDER BY class_id;

• 若班级学生不足3人，对应的top2/top3列会显示NULL，符合实际场景。
• 这里用MAX()聚合是因为每个rank_num在同一班级内唯一，用MIN()也能得到相同结果。

补充：数据库专属PIVOT写法（可选） #

部分数据库（如SQL Server、Oracle）支持PIVOT语法，也可以实现需求，但通用性不如条件聚合：

-- SQL Server示例
WITH ranked_students AS (
    SELECT
        c.class_id,
        c.class_name,
        s.first_name,
        s.age,
        'top' + CAST(ROW_NUMBER() OVER (PARTITION BY c.class_id ORDER BY s.age DESC) AS VARCHAR) + '_first_name' AS name_col,
        'top' + CAST(ROW_NUMBER() OVER (PARTITION BY c.class_id ORDER BY s.age DESC) AS VARCHAR) + '_age' AS age_col
    FROM classes c
    JOIN student_classes sc ON c.class_id = sc.class_id
    JOIN student s ON sc.student_id = s.student_id
)
SELECT class_id, class_name, top1_first_name, top2_first_name, top3_first_name, top1_age, top2_age, top3_age
FROM ranked_students
PIVOT (
    MAX(first_name) FOR name_col IN (top1_first_name, top2_first_name, top3_first_name)
) p1
PIVOT (
    MAX(age) FOR age_col IN (top1_age, top2_age, top3_age)
) p2
ORDER BY class_id;

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