mysql - 使用 MAX 命令返回上次订购的产品。 SQL

Question

目前我有以下结果集。

StockMovementDate       ProductId    TransactionReference
2014-01-09 00:00:03     1324         SO56777
2014-01-09 00:00:02     1324         PSA12556
2014-01-09 00:00:01     1324         NSA637
2014-01-06 00:00:01     1325         SO56774
2014-01-05 00:00:03     1324         NSA623
2014-01-05 00:00:02     1326         NSA622
2014-01-05 00:00:01     1326         SO56771

我将如何接收最新的 StockMovementDate，以及相关的 ProductId 和 TransactionReference？

StockMovementDate       ProductId    TransactionReference
2014-01-09 00:00:03     1324         SO56777
2014-01-06 00:00:01     1325         SO56774
2014-01-05 00:00:02     1326         NSA622

如果我使用 MAX，那么它没有什么区别，因为 TransactionReference 是唯一的，它仍然会带回整个列表。

select 
transactionheader.TransactionDate as StockMovementDate, 
product.id as ProductId,
transactionheader.reference 
from 
transactionheader left outer join
transactionline on transactionline.transactionheaderid = transactionheader.id left outer join
product on transactionline.productid = product.id 

Answer 1

这是一种方法:

SELECT A.*
FROM YourTable A
INNER JOIN (SELECT ProductId, MAX(StockMovementDate) StockMovementDate
            FROM YourTable
            GROUP BY ProductId) B
    ON A.ProductId = B.ProductId
    AND A.StockMovementDate = B.StockMovementDate