如何结合使用GROUP BY与JOIN查询学生平均年龄最高的课程

解决思路 #

你已经完成了两表关联的基础步骤，后续只需要补充分组聚合、排序/过滤的逻辑即可，核心步骤如下：

• 基于关联后的结果按course_id分组，用AVG()函数计算每个课程的学生平均年龄
• 若仅需取任意一个最高平均年龄的课程，直接对平均年龄降序排序后取第一条即可
• 若需取出所有并列最高平均年龄的课程，先计算所有课程的平均年龄，再过滤出和最高平均年龄相等的课程即可

基础实现（仅取1个最高平均年龄课程） #

SELECT 
    stc.course_id,
    AVG(s.age) AS avg_student_age
FROM STUDENTS_TO_COURSE stc
INNER JOIN Students s 
    ON stc.student_id = s.id
GROUP BY stc.course_id
ORDER BY avg_student_age DESC
LIMIT 1;

注：这里给表设置了简化别名stc和s，SQL可读性更高

兼容并列最高场景的实现 #

如果存在多个课程平均年龄相同且都是最高的情况，使用上述LIMIT 1只会返回其中一个，如需返回所有符合条件的课程，可以用CTE实现：

-- 先计算每个课程的平均年龄
WITH course_avg AS (
    SELECT 
        stc.course_id,
        AVG(s.age) AS avg_age
    FROM STUDENTS_TO_COURSE stc
    INNER JOIN Students s ON stc.student_id = s.id
    GROUP BY stc.course_id
)
-- 过滤出所有平均年龄等于最高值的课程
SELECT course_id, avg_age
FROM course_avg
WHERE avg_age = (SELECT MAX(avg_age) FROM course_avg);

如果使用的是不支持CTE的低版本数据库，将上面的CTE替换为嵌套子查询即可实现相同效果

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