Подзапрос встроенного представления SQL

Question

Можно ли ссылаться на встроенное представление, определенное в предложении "FROM", из подзапроса в предложении WHERE?

SELECT tmp.TeacherName,
       tmp.courseid,
       tmp.AvgAttendingStudents
FROM   (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
        GROUP  BY T.TeacherID,
                  C.CourseID) AS tmp
WHERE  tmp.AvgAttendingStudents = (SELECT max(AvgAttendingStudents)
                                   FROM   tmp AS tmp2
                                   WHERE  tmp2.TeacherID = tmp.TeacherID);  

В этом примере я пытаюсь перечислить всех учителей, и для каждого из них я хочу показать курс с максимальным средним числом посещающих студентов (рассчитанным на всех уроках). Я пытался использовать встроенное представление (tmp) для вычисления среднего числа посещающих студентов для каждого курса, но я не знаю, смогу ли я сослаться на это представление в подзапросе SELECT max(...). Мне нужно это для работы с Oracle, но, к сожалению, на данный момент у меня нет базы данных Oracle, чтобы попробовать это. Я пытался с MySQL (так как я не думаю, что я использую какие-либо специфичные для Oracle функции), но, как и ожидалось, я получаю сообщение об ошибке "Таблица" db.tmp "не существует". Возможно ли это как-то с Oracle?

Вот мой пример схемы:

CREATE TABLE Courses
  (
     CourseID   INTEGER PRIMARY KEY,
     CourseName VARCHAR(32),
     TeacherID  INTEGER
  );

CREATE TABLE Teachers
  (
     TeacherID   INTEGER PRIMARY KEY,
     TeacherName VARCHAR(32)
  );

CREATE TABLE Lessons
  (
     LessonDate        TIMESTAMP,
     CourseID          INTEGER,
     AttendingStudents INTEGER,
     PRIMARY KEY (LessonDate, CourseID)
  );  

(Извините за мой плохой английский)

Answer 1

Вы правы в том, что вы не можете ссылаться на производную таблицу ("встроенное представление") таким образом. Вам необходимо переписать производную таблицу ("встроенное представление") в общее табличное выражение:

У вас также есть другие ошибки там. В производной таблице вы переименуете 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);

Однако вышесказанное не будет работать в Oracle из-за неправильного использования group by и агрегатная функция. Выше приведено "ORA-00979: не выражение GROUP BY", см. Этот 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 для проверки данных.

Answer 2

Вы можете использовать пункт "иметь", который может предоставить вам способ ограничения агрегированной функции.

Вот пример:

    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
    GROUP  BY T.TeacherID,
              T.TeacherName
              C.CoursesID
    HAVING  avg(L.AttendingStudents) = (SELECT max(AvgAttendingStudents)
                               FROM   Teachers AS tmp2
                               WHERE  tmp2.TeacherID = T.TeacherID);

Я просто удаляю ваш первый вложенный запрос и изменяю AvgAttendingStudents на avg(L.AttendingStudents) (потому что вы не можете работать с переменной в предложении Have) и добавляете выбранные атрибуты в предложение Group, я не проверяю, но здесь способ сделать трюк.

Не забудьте добавить свой неагрегированный var, выбранный в групповом предложении.

Вот документация о наличии пункта.