mysql - 如何有效地选择与特定列的最大值对应的行？

Question

我有一个问题:

SELECT * FROM `InvoiceLines`
INNER JOIN Invoices ON Invoices.`invoiceID` = InvoiceLines.`invoiceID`
INNER JOIN `DistributorOrdersTbl` ON DistributorOrdersTbl.`distOrderRecID` = Invoices.`orderID`
WHERE DistributorOrdersTbl.`distOrderRecID` = 3829
AND InvoiceLines.`qty` > 0

返回一些发票:

我只想选择最新 ID 2800 的发票。我最终采用的方式是复制粘贴:

SELECT * FROM `InvoiceLines`
INNER JOIN Invoices ON Invoices.`invoiceID` = InvoiceLines.`invoiceID`
INNER JOIN `DistributorOrdersTbl` ON DistributorOrdersTbl.`distOrderRecID` = Invoices.`orderID`
WHERE DistributorOrdersTbl.`distOrderRecID` = 3829
AND InvoiceLines.`qty` > 0
AND Invoices.invoiceID = (
        SELECT MAX(Invoices.invoiceID) FROM `InvoiceLines`
        INNER JOIN Invoices ON Invoices.`invoiceID` = InvoiceLines.`invoiceID`
        INNER JOIN `DistributorOrdersTbl` ON DistributorOrdersTbl.`distOrderRecID` = Invoices.`orderID`
        WHERE DistributorOrdersTbl.`distOrderRecID` = 3829
        AND InvoiceLines.`qty` > 0
    )

果然，正确的发票被退回了。然而，感觉好像这是低效的，因为我实际上是在执行查询两次。这样做的最佳方法是什么？我尝试了以下但似乎这是不正确的 SQL:

SELECT * FROM `InvoiceLines`
INNER JOIN Invoices ON Invoices.`invoiceID` = InvoiceLines.`invoiceID`
INNER JOIN `DistributorOrdersTbl` ON DistributorOrdersTbl.`distOrderRecID` = Invoices.`orderID`
WHERE DistributorOrdersTbl.`distOrderRecID` = 3829
AND InvoiceLines.`qty` > 0
AND Invoices.invoiceID = MAX(Invoices.invoiceID)

返回“组函数使用无效”。

提前致谢!

Answer 1

假设所有发票都有发票行，您可以仅在 Invoices 上使用子查询:

SELECT *
FROM (SELECT i.*
      FROM Invoices i
      WHERE i.orderID = 3829
      ORDER BY i.invoiceID DESC
      LIMIT 1
     ) i JOIN
     InvoiceLines il
     ON i.invoiceID = il.invoiceID JOIN
     DistributorOrdersTbl d
     ON d.distOrderRecID = i.orderID
WHERE il.qty > 0;