Эта форма моего коррелированного подзапроса выдает сообщение об ошибке «Неизвестный столбец« 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 и поиск в Интернете не пролили свет на этот вопрос.
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.
FWIW, я не думаю, что это связано с использованием GROUP BY. На связанной странице руководства есть примеры, в которых не используются агрегатные функции. Это связано со ссылкой на Invoices.TranDate
в подзапросе производной таблицы.
Ах я вижу. В примерах dev.mysql.com/doc/refman/8.0/en/correlated-subqueries.html используется подзапрос в предложении where. Меня смущает абзац Scoping, показывающий пример подзапроса в LEFT OUTER JOIN. TBH Я до сих пор не понимаю, что делает эту таблицу LATERAL производной - JOIN? Но тогда это производная таблица.
Это производная таблица, которая также является коррелированным подзапросом. т.е. это производная таблица, которая включает ссылку на другую объединенную таблицу из внешнего запроса.
Спасибо Билл! Теперь я понял, поскольку он использует группировку по функциям, поэтому подзапрос является боковой производной таблицей. Я не видел этого в образцах документации. Ваш код работает отлично.