Псевдоним таблицы неизвестен во вложенном подзапросе

Следующий запрос работает просто отлично. Я использую значение из внешнего выбора для фильтрации внутри внутреннего выбора.

SELECT
    bk.ID,
    (SELECT COUNT(*) FROM guests WHERE BookingID = bk.ID) as count
FROM
    bookings bk;

Однако следующий выбор не будет работать:

SELECT
    bk.ID,
    (SELECT SUM(count) FROM (SELECT COUNT(*) AS count FROM guests WHERE BookingID = bk.ID GROUP BY RoomID) sub) as sumcount
FROM
    bookings bk;

Сообщение об ошибке: Error Code: 1054. Unknown column 'bk.ID' in 'where clause'

Почему мой псевдоним bk известен в подвыборке, но не в подвыборке вложенной выборки?

Для справки, я использую MySQL 5.6.


mysql sql
person Jan Dörrenhaus    schedule 17.06.2015    source источник
comment
Какова логика второго запроса — вы хотите произвести отдельные подсчеты для каждого RoomID, но затем отбросить всю информацию о комнате и просто сложить их вместе? Не могли бы вы добавить некоторые примеры данных и ожидаемые результаты?   -  person Damien_The_Unbeliever    schedule 17.06.2015
comment
Это ОЧЕНЬ сокращенный и упрощенный пример, часть очень большой и очень сложной схемы данных. Проще говоря, предполагается, что самый внутренний выбор должен подсчитывать вхождения по дням, а выбор суммы должен суммировать их за каждый месяц. Да, это звучит лишним, но, к сожалению, это не так. Это нужно делать так. Однако я не ищу другого способа сделать это, мне интересно, почему внутренний выбор вдруг больше не распознает псевдоним из внешнего выбора.   -  person Jan Dörrenhaus    schedule 17.06.2015
comment
Я подозреваю, что проблема в том, что он используется в подзапросе, определенном в предложении FROM, в отличие от вашего оригинала, где вы ссылаетесь на него в предложении WHERE. Но не уверен на 100%.   -  person Damien_The_Unbeliever    schedule 17.06.2015
comment
Стандартный SQL просто не допускает корреляции в производной таблице. Вы должны переписать его, используя левое соединение с производной таблицей.   -  person dnoeth    schedule 17.06.2015
comment
@dnoeth Если это просто не разрешено, почему работает первый выбор?   -  person Jan Dörrenhaus    schedule 17.06.2015
comment
В первом выборе вы получили коррелированный скалярный подзапрос, но во втором выберите производную таблицу FROM (this is a Derived Table) sub в скалярном подзапросе.   -  person dnoeth    schedule 17.06.2015

Ответы (2)


arrow_upward
3
arrow_downward

Коррелированные скалярные подзапросы в списке SELECT обычно можно переписать в LEFT JOIN в производной таблице (и во многих случаях они могут работать лучше):

SELECT
    bk.ID,
    dt.sumcount
FROM
    bookings bk
LEFT JOIN 
 (SELECT BookingID,SUM(COUNT) AS sumcount
  FROM
   (
     SELECT BookingID, RoomId, COUNT(*) AS COUNT 
     FROM guests 
     GROUP BY BookingID, RoomID
    ) sub
  ) AS dt
ON bk.BookingID = dt.BookingID
person dnoeth    schedule 17.06.2015

arrow_upward
3
arrow_downward

Это называется "обзор". Я знаю, что Oracle (например) смотрит только на один уровень для разрешения псевдонимов таблиц. SQL Server также последователен: он работает более чем на одном уровне.

Исходя из этого примера, MySQL явно ограничивает область действия идентификатора bk непосредственным подзапросом. В документации есть небольшая подсказка (выделено мой):

Коррелированный подзапрос — это подзапрос, содержащий ссылку на таблицу, которая также появляется во внешнем запросе .

Однако я не нашел другой конкретной ссылки на правила области видимости в документации. Есть и другие ответы (здесь и < href="https://stackoverflow.com/questions/2435644/mysql-scoping-problem-with-correlated-subqueries">здесь), которые указывают, что область действия псевдонима таблицы ограничена одним уровнем подзапрос.

Вы уже знаете, как решить проблему (ваши два запроса должны дать одинаковые результаты). Эту проблему также можно решить путем перекомпоновки запроса с объединениями и агрегациями.

person Gordon Linoff    schedule 17.06.2015