MySQL 相关子查询表名超出范围

Question

我的相关子查询的这种形式出现错误消息“‘where 子句’中的未知列‘Invoices.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/terior-driven-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。