mysql - 使用两个字段作为标识符获取表中的最新记录

Question

我正在尝试查找商店中特定位置的最新订单。因此，每个商店都有多个订单发送到商店内的某个位置。

这是我的表格:

LocationId  StoreId OrderStreet     OrderCity   PCode   OrderState      OrderDate          
37          494     Store           Store       3000    NT              11/13/2013 2:43:04 PM
38          494     Store           Store       3000    NSW             12/04/2013 11:32
41          496     Underword       Narrabeen   2100    NSW             12/04/2013 11:37
38          494     Store           Store       3000    NT              12/09/2013 10:47
38          494     Store           Store       3000    NT              12/09/2013 10:51
40          496     Underword       Narrabeen   2100    NSW             3/03/2014 8:5
42          497     345 Malltomall  Sydney      3999    NSW             4/01/2014 15:17
42          497     345 Malltomall  Sydney      3999    NSW             4/01/2014 16:58 92
41          496     Underword       Narrabeen   2100    NSW             4/01/2014 17:14
43          497     345 Malltomall  Sydney      3999    NSW             4/02/2014 15:21

这是我到目前为止所做的:
这为我提供了每个商店的最新订单，但仅返回每个商店的 1 个位置。

SELECT        A.OrderDate, A.OrderId, A.StoreId, A.LocationId, A.OrderStreet, A.OrderCity, A.OrderState, A.OrderPostCode, A.StoreId + A.LocationId AS Sum1
FROM            tblOrder AS A INNER JOIN
                              (SELECT   StoreId, MAX(OrderDate) AS MaxDate
                              FROM     tblOrder
                              GROUP BY StoreId) AS B 
                              ON A.StoreId = B.StoreId AND A.OrderDate = B.MaxDate

这是它返回的内容:我想我可以将这些列添加在一起以获得唯一的代码，例如。 Sum1 但我无法让它工作。

Orderdate     StoreId  LocationId OrderStreet       OrderCity   OrderState  Pcode   Sum1
4/02/2014     497      43         345 Malltomall    Sydney      NSW         3999    540
4/01/2014     496      41         Underword         Narrabeen   NSW         2100    537
12/09/2013    494      38         Store             Store       NT          3000    532

天哪，我感觉很蠢......

Answer 1

按 LocationId 以及 StoreId 对派生表进行分组，然后也连接回该列上的原始表。我认为像这样的分组最大值查询是 NATURAL JOIN 的完美用例(它更简洁，因为它不需要显式谓词，而是连接在两个表):

SELECT * FROM tblOrder NATURAL JOIN (
  SELECT   StoreId, LocationId, MAX(OrderDate) AS OrderDate
  FROM     tblOrder
  GROUP BY StoreId, LocationId
) t

或者，如果 LocationId 在所有商店中都是唯一的，您只需对该列进行分组/联接(并从派生表中完全删除 StoreId):

SELECT * FROM tblOrder NATURAL JOIN (
  SELECT   LocationId, MAX(OrderDate) AS OrderDate
  FROM     tblOrder
  GROUP BY LocationId
) t