您是正确的，您不能以这种方式引用派生表(“内联视图”)。您需要将派生表(“内联视图”)重写为公共表表达式：

你还有其他错误在里面。在派生表中，将 TeacherID 重命名为 ID ，将 TeacherName 重命名为 Name ，因此需要使用这些列名，而不是“真实的”列名。

此外，Oracle不支持表别名的 AS ，因此您也需要消除这些别名。

因此，对该声明的直接重写将是：

with tmp as (
  SELECT T.TeacherID              AS ID,
         T.TeacherName            AS Name,
         C.CourseID               AS CourseID,
         avg(L.AttendingStudents) AS AvgAttendingStudents
  FROM   Teachers T
         join Courses C
           ON C.TeacherID = T.TeacherID
         join Lessons L
           ON L.CourseID = C.CourseID
  GROUP  BY T.TeacherID,
            C.CourseID
SELECT tmp.name,
       tmp.courseid,
       tmp.AvgAttendingStudents
FROM tmp
where tmp.AvgAttendingStudents = (SELECT max(AvgAttendingStudents)
                                  FROM   tmp tmp2
                                  WHERE  tmp2.id = tmp.id);

但是，由于 和聚合函数的无效使用，上述 在 group by 中不能工作。以上结果将导致"ORA-00979:不是按表达式分组“，请参见此 SQLFiddle 。

为此，您需要在CTE中使用一个窗口函数，并去掉 group by ：

with tmp as (
  SELECT T.TeacherID              AS ID,
         T.TeacherName            AS Name,
         C.CourseID               AS CourseID,
         avg(L.AttendingStudents) over (partition by t.teacherid, c.courseid) AS avgattendingstudents 
  FROM   Teachers T
         join Courses C
           ON C.TeacherID = T.TeacherID
         join Lessons L
           ON L.CourseID = C.CourseID
SELECT tmp.name,
       tmp.courseid,
       tmp.AvgAttendingStudents
FROM tmp
where tmp.AvgAttendingStudents = (SELECT max(AvgAttendingStudents)
                                  FROM   tmp tmp2
                                  WHERE  tmp2.id = tmp.id);

有关示例，请参阅此 SQLFiddle 。

请注意，您不能用MySQL测试上述查询，因为它不支持像普通表表达式或窗口函数一样的 现代SQL 。

但是您可以使用SQLFiddle示例来用数据测试它。

您可以使用which子句，它可以为您提供一种限制agregate函数的方法。

这里有一个例子：

    SELECT T.TeacherID              AS ID,
           T.TeacherName            AS Name,
           C.CourseID               AS CourseID,
           avg(L.AttendingStudents) AS AvgAttendingStudents
    FROM   Teachers AS T
           join Courses AS C
             ON C.TeacherID = T.TeacherID
           join Lessons AS L
             ON L.CourseID = C.CourseID