SQL技术咨询：选取具有最大平均值的记录时遇到难题求助

我来帮你搞定这个选取最大平均值记录的问题！先理一理你的场景：你已经筛选出了只教授BASI DI DATI领域课程的老师，并且关联了他们每门课的平均学生数（GVA），现在要从这个结果里找出GVA最高的那条（或那些）记录，对吧？

首先得说，你的基础查询已经把符合条件的老师和课程平均数据关联起来了，但缺少了“锁定最大值”的筛选逻辑。下面给你两种实用的解决方案，适配大多数主流SQL数据库：

方案一：用子查询获取最大值后筛选 #

这种方法逻辑直观，先把你的基础查询封装成一个临时数据集，再找出这个数据集里的最大GVA，最后筛选出等于该最大值的记录：

WITH TeacherCourseAvg AS (
    SELECT 
        DOC.TEACHERID,
        L.COURSEID,
        L.GVA
    FROM (
        -- 筛选只教"BASI DI DATI"的老师
        SELECT MATRDOC AS TEACHERID 
        FROM DOCENTE 
        WHERE MATRDOC NOT IN (
            SELECT MATRDOC 
            FROM CORSO 
            WHERE AREA <> 'BASI DI DATI'
        )
    ) DOC
    -- 显式JOIN替换原隐式逗号连接，可读性更好
    JOIN (
        -- 计算每个老师每门课的平均学生数
        SELECT 
            C.CODCORSO AS COURSEID, 
            MATRDOC AS TEACHERID, 
            AVG(NUMSTUDENTI) AS GVA 
        FROM CORSO C
        JOIN LEZIONE L ON C.CODCORSO = L.CODCORSO 
        GROUP BY C.CODCORSO, MATRDOC
    ) L ON DOC.TEACHERID = L.TEACHERID
)
-- 筛选出GVA等于最大值的记录
SELECT *
FROM TeacherCourseAvg
WHERE GVA = (SELECT MAX(GVA) FROM TeacherCourseAvg);

如果你的数据库不支持CTE（比如MySQL 5.7及更早版本），把CTE换成嵌套子查询就行，效果完全一致。

方案二：用窗口函数实现更灵活的排序筛选 #

如果你需要处理并列最大值的场景（比如多个记录GVA相同且都是最高），窗口函数会更方便：

WITH TeacherCourseAvg AS (
    SELECT 
        DOC.TEACHERID,
        L.COURSEID,
        L.GVA,
        -- 按GVA降序排名，并列最大值会得到相同的排名
        RANK() OVER (ORDER BY L.GVA DESC) AS RankNum
    FROM (
        SELECT MATRDOC AS TEACHERID 
        FROM DOCENTE 
        WHERE MATRDOC NOT IN (
            SELECT MATRDOC 
            FROM CORSO 
            WHERE AREA <> 'BASI DI DATI'
        )
    ) DOC
    JOIN (
        SELECT 
            C.CODCORSO AS COURSEID, 
            MATRDOC AS TEACHERID, 
            AVG(NUMSTUDENTI) AS GVA 
        FROM CORSO C
        JOIN LEZIONE L ON C.CODCORSO = L.CODCORSO 
        GROUP BY C.CODCORSO, MATRDOC
    ) L ON DOC.TEACHERID = L.TEACHERID
)
-- 只取排名第一的记录
SELECT TEACHERID, COURSEID, GVA
FROM TeacherCourseAvg
WHERE RankNum = 1;

• 用RANK()会保留并列排名，比如两个记录都是最大值，都会被选中；
• 如果只想选其中一条，换成ROW_NUMBER()即可，但它会给并列记录分配不同的序号，需要根据你的需求选择。

额外小提示：避免NOT IN的空值陷阱 #

如果CORSO表的MATRDOC字段存在空值，NOT IN会返回空结果，导致你筛选不出任何老师。这种情况下，建议把NOT IN改成NOT EXISTS，逻辑更安全：

SELECT MATRDOC AS TEACHERID 
FROM DOCENTE D
WHERE NOT EXISTS (
    SELECT 1 
    FROM CORSO C
    WHERE C.MATRDOC = D.MATRDOC 
    AND C.AREA <> 'BASI DI DATI'
)

内容的提问来源于stack exchange，提问作者Salvatore Gabriele La Greca