如果我对你的问题理解正确，我想这应该行得通

SELECT teacherid, courseid, max(gva) ------*****changed
   FROM (SELECT MATRDOC AS TEACHERID
         FROM DOCENTE
         WHERE MATRDOC NOT IN (SELECT MATRDOC
                               FROM CORSO
                               WHERE AREA <> 'BASI DI DATI')) DOC,
        (SELECT C.CODCORSO AS COURSEID, 
                MATRDOC AS TEACHERID, 
                AVG(NUMSTUDENTI) AS GVA
        FROM CORSO C, LEZIONE L
        WHERE C.CODCORSO = L.CODCORSO
        GROUP BY C.CODCORSO, MATRDOC) L
   WHERE DOC.TEACHERID = L.TEACHERID
   group by teacherid;  -----*********added

学生的平均人数是“gva”，这似乎很奇怪，但还行。
在ansisql中，可以使用 fetch first 条款：

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
ORDER BY GVA DESC
FETCH FIRST 1 ROW ONLY;

并非所有数据库都支持 FETCH FIRST . 常见的替代方案包括 SELECT TOP 以及 LIMIT .
编辑：
如果你想要每个老师的最大值，那么使用 ROW_NUMBER() :

SELECT *
FROM (SELECT C.CODCORSO AS COURSEID, MATRDOC AS TEACHERID, AVG(NUMSTUDENTI) AS GVA,
             ROW_NUMBER() OVER (PARTITION BY TEACHERID ORDER BY AVG(NUMSTUDENTI) DESC) as seqnum
      FROM CORSO C JOIN
           LEZIONE L
           ON C.CODCORSO = L.CODCORSO
      GROUP BY C.CODCORSO, MATRDOC
     ) cl
WHERE seqnum = 1
ORDER BY GVA DESC;

这是所有ansi标准语法，包括正确使用 JOIN .