通过子查询中的最大日期来连接三张表

后端开发 2026-07-09

我有三张表，orders、invoices和 invoice lines。

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

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

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

一个简单的JOIN会显示所有数据，对于订单号102，预计会有一条重复的记录。

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

与重复记录不同，我只想看到最近的发票日期。我在Stack Overflow上看到这个后，尝试了以下方法。

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
INNER JOIN (
  SELECT purchaseOrderNumber, MAX(invoiceDate) AS invoiceDate
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
  GROUP BY purchaseOrderNumber
) AS max USING (purchaseOrderNumber, invoiceDate)

这给出了订单号102的期望输出。但对于没有相关发票明细/发票的订单，则不会显示任何行。

解决方案

问题在于：使用INNER JOIN会丢弃未开具发票的订单——改用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
LEFT JOIN (
  SELECT 
    il.purchaseOrderNumber, 
    MAX(i.invoiceDate) AS max_invoiceDate
  FROM tbl_fruit_InvoiceLines il
  JOIN tbl_fruit_invoices i ON il.invoiceNumber = i.invoiceNumber
  GROUP BY il.purchaseOrderNumber
) AS max ON tbl_fruit_orders.OrderNumber = max.purchaseOrderNumber
WHERE tbl_fruit_invoices.invoiceDate = max.max_invoiceDate 
   OR tbl_fruit_invoices.invoiceDate IS NULL;

第二种获取它的方法是使用ROW_NUMBER（为每个订单的最近发票分配1，然后简单的WHERE条件筛选）：

WITH RankedInvoices AS (
  SELECT 
    o.OrderNumber,
    o.product_id AS order_product_id,
    o.product_name,
    o.quantityOnOrder,
    il.invoiceNumber,
    il.quantityInvoiced,
    i.invoiceDate,
    i.invoiceNotes,
    i.carrier,
    i.trackingReference,
    ROW_NUMBER() OVER(PARTITION BY o.OrderNumber ORDER BY i.invoiceDate DESC) as rn
  FROM tbl_fruit_orders o
  LEFT JOIN tbl_fruit_InvoiceLines il ON o.OrderNumber = il.purchaseOrderNumber
  LEFT JOIN tbl_fruit_invoices i ON il.invoiceNumber = i.invoiceNumber
)
SELECT *
FROM RankedInvoices 
WHERE rn = 1;

SQL