SQL练习中如何获取最大平均出勤值对应的CCode字段值

做SQL练习题时，卡在「返回学生平均出勤人数最高的对应CCode」步骤，相关参考截图如下：


原有实现代码如下，已经发现WHERE子句中直接使用MAX聚合函数求最大值的写法无法正常生效：

SELECT CO.TCode, CO.CCode
FROM COURSE CO
WHERE CO.CCode NOT IN(
  SELECT CCode
  FROM COURSE
  WHERE Topic <> 'database')
AND CO.CCode =(
  SELECT C1.CCode
  FROM (SELECT CCode, AVG(AttendingStudent#) MEDIA
       FROM LECTURE
       GROUP BY CCode) C1
  WHERE MAX(C1.MEDIA) AND C1.CCode = CO.CCode
)

原有代码的核心问题有两个：

• 聚合函数MAX()不能直接写在WHERE子句中作为判断条件，聚合结果的筛选要么放在HAVING子句，要么单独计算出最大值后再做匹配
• 内层子查询错误关联了外层表的CO.CCode，导致无法计算全局维度的最高平均出勤值，逻辑上无法拿到正确结果
  另外原有筛选数据库主题课程的NOT IN写法可读性差，且存在子查询返回NULL值时逻辑失效的风险，直接用等值判断Topic = 'database'更高效可靠。

兼容所有SQL版本的通用写法 #

逻辑为先计算所有课程的平均出勤人数，找到最高的平均出勤值，再匹配筛选出主题为database、平均出勤等于最高值的课程：

SELECT CO.TCode, CO.CCode
FROM COURSE CO
INNER JOIN (
    SELECT 
        CCode,
        AVG(AttendingStudent#) AS AvgAttending
    FROM LECTURE
    GROUP BY CCode
) L ON CO.CCode = L.CCode
WHERE CO.Topic = 'database'
AND L.AvgAttending = (
    SELECT MAX(AvgAttending)
    FROM (
        SELECT AVG(AttendingStudent#) AS AvgAttending
        FROM LECTURE
        GROUP BY CCode
    ) t
)

支持窗口函数的简化写法（MySQL 8.0+、PostgreSQL、SQL Server等适用） #

用RANK()窗口函数直接按平均出勤倒序排名，取排名为1的记录即可，天然支持多个课程并列最高的场景：

WITH CourseAttendStat AS (
    SELECT
        CCode,
        AVG(AttendingStudent#) AS AvgAttending,
        RANK() OVER (ORDER BY AVG(AttendingStudent#) DESC) AS AttendRank
    FROM LECTURE
    GROUP BY CCode
)
SELECT CO.TCode, CO.CCode
FROM COURSE CO
INNER JOIN CourseAttendStat stat ON CO.CCode = stat.CCode
WHERE CO.Topic = 'database'
AND stat.AttendRank = 1

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