MySQL关联的子查询表名称超出范围

Question

这种形式的关联子查询会出现错误消息"未知列"发票"。"where子句"中的TranDate"；

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文档和互联网搜索并没有对此事做出任何解释。

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html说：

派生表通常不能引用(依赖于(同一FROM子句中前面表的列。从MySQL 8.0.14开始，派生表可以定义为横向派生表，以指定允许此类引用。

在SQL:1999中，如果派生表前面有LATERAL关键字(意思是"此派生表依赖于其左侧以前的表"(，则查询将合法：

我还没有测试过，但我相信你的查询可以这样写：

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。