使用含MAX日期的子查询关联三张表，如何保留无发票订单？

现有三张表：tbl_fruit_orders、tbl_fruit_invoices、tbl_fruit_InvoiceLines，表结构及数据如下：

tbl_fruit_orders #

uid OrderNumber product_id  product_name    quantityOnOrder 
1   101         p001        Apple           6   
2   102         p002        Pear            20  
3   103         p003        Orange          9   

tbl_fruit_invoices #

id  invoiceNumber   invoiceDate invoiceNotes    carrier trackingReference   
1   i600            2026-05-01  Part Supply     FedEx   5874563 
2   i601            2026-05-28  Part Supply     FedEx   7893654 

tbl_fruit_InvoiceLines #

id  invoiceNumber   product_id  purchaseOrderNumber quantityInvoiced    
1   i600            p002        102                 5   
2   i601            p002        102                 15  

使用简单LEFT JOIN查询时：

SELECT * 
FROM tbl_fruit_orders
LEFT JOIN tbl_fruit_InvoiceLines ON tbl_fruit_orders.OrderNumber = tbl_fruit_InvoiceLines.purchaseOrderNumber
LEFT JOIN tbl_fruit_invoices ON tbl_fruit_InvoiceLines.invoiceNumber = tbl_fruit_invoices.invoiceNumber

会出现OrderNumber为102的重复行。期望仅显示每个订单的最新invoiceDate，尝试的SQL能得到102的正确结果，但无法显示无关联发票行/发票的订单，需修改以保留这类订单。

问题出在原SQL最后一步使用了INNER JOIN，它会过滤掉没有匹配到最大发票日期的订单（即无发票的订单）。以下提供两种修改方案：

方案1：替换INNER JOIN为LEFT JOIN并优化子查询 #

SELECT fo.*, fil.*, fi.*
FROM tbl_fruit_orders fo
LEFT JOIN tbl_fruit_InvoiceLines fil ON fo.OrderNumber = fil.purchaseOrderNumber
LEFT JOIN tbl_fruit_invoices fi ON fil.invoiceNumber = fi.invoiceNumber
LEFT JOIN (
  -- 子查询直接获取每个采购订单的最新发票日期
  SELECT fil_inner.purchaseOrderNumber, MAX(fi_inner.invoiceDate) AS max_invoiceDate
  FROM tbl_fruit_InvoiceLines fil_inner
  LEFT JOIN tbl_fruit_invoices fi_inner ON fil_inner.invoiceNumber = fi_inner.invoiceNumber
  GROUP BY fil_inner.purchaseOrderNumber
) AS max_dates ON fo.OrderNumber = max_dates.purchaseOrderNumber
-- 过滤条件：要么是无发票的订单，要么是匹配到最新发票日期的记录
WHERE max_dates.max_invoiceDate IS NULL 
   OR fi.invoiceDate = max_dates.max_invoiceDate;

方案2：使用窗口函数（更简洁高效） #

窗口函数可以直接按订单分组排序，保留每个订单的最新发票记录，同时自动保留无发票的订单：

SELECT *
FROM (
  SELECT 
    fo.*, fil.*, fi.*,
    -- 按订单分组，给每个订单的发票记录按日期降序排名
    ROW_NUMBER() OVER (PARTITION BY fo.OrderNumber ORDER BY fi.invoiceDate DESC) AS rn
  FROM tbl_fruit_orders fo
  LEFT JOIN tbl_fruit_InvoiceLines fil ON fo.OrderNumber = fil.purchaseOrderNumber
  LEFT JOIN tbl_fruit_invoices fi ON fil.invoiceNumber = fi.invoiceNumber
) AS ranked
-- 只保留每个订单的第一条（最新发票，无发票的订单rn为1）
WHERE rn = 1;

无发票的订单因未关联到发票行和发票表，所有发票相关字段会显示为NULL，同时rn值为1，会被正常保留。

内容的提问来源于stack exchange，提问作者James Smith