MySQL Коррелированное имя таблицы подзапросов выходит за рамки

Эта форма моего коррелированного подзапроса выдает сообщение об ошибке «Неизвестный столбец« Invoices.TranDate »в« предложении where »».

select InvoiceID, TranDate
    , ifnull(TotPayments,0) TotPayments, ifnull(CountPayments,0) CountPayments
from Invoices
    left join (select DebtorID, sum(TranAmount) TotPayments, count(*) CountPayments
        from CashTrans 
        where CashTrans.TranDate >= Invoices.TranDate
        group by DebtorID) PY on PY.DebtorID = Invoices.DebtorID

Но эта версия работает

select InvoiceID, TranDate
    , (select sum(TranAmount) from CashTrans 
        where CashTrans.TranDate >= Invoices.TranDate
            and CashTrans.DebtorID = Invoices.DebtorID) TotPayments
    , (select count(*) from CashTrans 
        where CashTrans.TranDate >= Invoices.TranDate
            and CashTrans.DebtorID = Invoices.DebtorID) CountPayments
from Invoices;

Что не так с первым запросом? Единственное, о чем я могу думать, это то, что в моей системе Windows я настроил lower_case_table_names=2, так как хочу сохранить имена в смешанном регистре. Возможно, это как-то связано с тем, что первый запрос не видит Invoice.TranDate в области видимости? Документация MySQL и поиск в Интернете не пролили свет на этот вопрос.

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
1
0
11
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html говорит:

A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted.

In SQL:1999, the query becomes legal if the derived tables are preceded by the LATERAL keyword (which means “this derived table depends on previous tables on its left side”):

Я не проверял это, но я считаю, что ваш запрос можно было бы написать так:

SELECT InvoiceID, TranDate,
  IFNULL(TotPayments,0) AS TotPayments,
  ifnull(CountPayments,0) AS CountPayments
FROM Invoices
LEFT JOIN LATERAL (
  SELECT DebtorID, 
    SUM(TranAmount) AS TotPayments, 
    COUNT(*) AS CountPayments
  FROM CashTrans 
  WHERE CashTrans.TranDate >= Invoices.TranDate
  GROUP BY DebtorID
) AS PY ON PY.DebtorID = Invoices.DebtorID;

Также имейте в виду, что для этого требуется, чтобы вы использовали как минимум MySQL 8.0.14.

Спасибо Билл! Теперь я понял, поскольку он использует группировку по функциям, поэтому подзапрос является боковой производной таблицей. Я не видел этого в образцах документации. Ваш код работает отлично.

Peter Brand 18.03.2022 16:36

FWIW, я не думаю, что это связано с использованием GROUP BY. На связанной странице руководства есть примеры, в которых не используются агрегатные функции. Это связано со ссылкой на Invoices.TranDate в подзапросе производной таблицы.

Bill Karwin 18.03.2022 16:38

Ах я вижу. В примерах dev.mysql.com/doc/refman/8.0/en/correlated-subqueries.html используется подзапрос в предложении where. Меня смущает абзац Scoping, показывающий пример подзапроса в LEFT OUTER JOIN. TBH Я до сих пор не понимаю, что делает эту таблицу LATERAL производной - JOIN? Но тогда это производная таблица.

Peter Brand 18.03.2022 16:59

Это производная таблица, которая также является коррелированным подзапросом. т.е. это производная таблица, которая включает ссылку на другую объединенную таблицу из внешнего запроса.

Bill Karwin 18.03.2022 17:01

Другие вопросы по теме