MySql获取具有最新日期的行

Question

下面是代码：

SELECT        tblitem.strItemCode, tblitem.strItemName, tblitemunit.strItemUnitName, tblvendor.strVendName, MAX(tblitemprice.dtmItemPasOf) AS Expr1, 
                         tblitemprice.dblItemPAmount
FROM            tblclassification INNER JOIN
                         tblitem ON tblclassification.strClasCode = tblitem.strItemClasCode INNER JOIN
                         tblitemprice ON tblitem.strItemCode = tblitemprice.strItemPItemCode INNER JOIN
                         tblitemunit ON tblitemprice.strItemPItemUnitCode = tblitemunit.strItemUnitCode INNER JOIN
                         tblvendor ON tblclassification.strClasCode = tblvendor.strVendClasCode AND tblitemprice.strItemPVendCode = tblvendor.strVendCode AND tblitem.deleted_at IS NULL
GROUP BY tblitem.strItemCode, tblitem.strItemName, tblitemunit.strItemUnitName, tblvendor.strVendName, tblitemprice.dblItemPAmount

结果是：

CODE     NAME        UNIT    VENDOR          DATE            PRICE

ITEM101-Fudgee Bar-Piece-Imus Palengke 10/9/20165:03:32AM - 6.5

ITEM102-Yum Burger-Box-Jollibee Lumina Mall-10/9/2016 6:13:27 AM  -    2500

ITEM102-Yum Burger-Piece-Jollibee Lumina Mall-10/9/2016 4:42:28 AM  -  30
ITEM102-Yum Burger-Piece-Jollibee Lumina Mall-10/13/2016 12:37:31 PM-  35

ITEM102-Yum Burger  Piece   Jollibee Lumina Mall    10/14/2016 10:05:44 PM  40

我想要的是只拿到最新价格的那一行。有人能帮帮我吗。

我想把Item101和最后一行取到ITEM102，因为它是最新的.

Answer 1

如果我理解正确的话，你正在寻找最后更新的行价格。这样做很容易：

1. 按相关时间戳(ORDER BY <FIELD_NAME> DESC)对数据进行排序。根据我从您的查询和结果中收集到的信息，Expr1是给定价格的最新日期。
2. 只选择一个元素(LIMIT 1)。由于您的数据已经按反向时间顺序排序，因此您肯定会选择最新的数据。

这方面的SQL将是

SELECT        tblitem.strItemCode, tblitem.strItemName, tblitemunit.strItemUnitName, tblvendor.strVendName, MAX(tblitemprice.dtmItemPasOf) AS Expr1, 
                         tblitemprice.dblItemPAmount
FROM            tblclassification INNER JOIN
                         tblitem ON tblclassification.strClasCode = tblitem.strItemClasCode INNER JOIN
                         tblitemprice ON tblitem.strItemCode = tblitemprice.strItemPItemCode INNER JOIN
                         tblitemunit ON tblitemprice.strItemPItemUnitCode = tblitemunit.strItemUnitCode INNER JOIN
                         tblvendor ON tblclassification.strClasCode = tblvendor.strVendClasCode AND tblitemprice.strItemPVendCode = tblvendor.strVendCode AND tblitem.deleted_at IS NULL
GROUP BY tblitem.strItemCode, tblitem.strItemName, tblitemunit.strItemUnitName, tblvendor.strVendName, tblitemprice.dblItemPAmount
ORDER BY Expr1 DESC
LIMIT 1

试试看！