SQL 语句帮助 - 为每个客户选择最新的订单

SQL Statement Help - Select latest Order for each Customer

提问by alextansc

Say I have 2 tables: Customers and Orders. A Customer can have many Orders.

假设我有 2 个表：Customers 和 Orders。一个客户可以有多个订单。

Now, I need to show any Customers with his latest Order. This means if a Customer has more than one Orders, show only the Order with the latest Entry Time.

现在，我需要向所有客户展示他的最新订单。这意味着如果客户有多个订单，则仅显示具有最新输入时间的订单。

This is how far I managed on my own:

这是我自己管理的程度：

SELECT a.*, b.Id
FROM Customer a INNER JOIN Order b ON b.CustomerID = a.Id
ORDER BY b.EntryTime DESC

This of course returns all Customers with one or more Orders, showing the latest Order first for each Customer, which is not what I wanted. My mind was stuck in a rut at this point, so I hope someone can point me in the right direction.

这当然会返回所有带有一个或多个订单的客户，首先为每个客户显示最新的订单，这不是我想要的。在这一点上，我的思绪陷入了困境，所以我希望有人能指出我正确的方向。

For some reason, I thinkI need to use the MAX syntax somewhere, but it just escapes me right now.

出于某种原因，我想我需要在某处使用 MAX 语法，但它现在只是让我逃避。

UPDATE:After going through a few answers here (there's a lot!), I realized I made a mistake: I meant anyCustomer with his latest record. That means if he does not have an Order, then I do not need to list him.

更新：经过这里的几个答案（有很多！），我意识到我犯了一个错误：我的意思是任何拥有最新记录的客户。这意味着如果他没有订单，那么我不需要列出他。

UPDATE2:Fixed my own SQL statement, which probably caused no end of confusion to others.

UPDATE2:修正了我自己的 SQL 语句，这可能会引起其他人的困惑。

回答by Martin Brown

I don't think you do want to use MAX() as you don't want to group the OrderID. What you need is a ordered sub query with a SELECT TOP 1.

我认为您不想使用 MAX()，因为您不想对 OrderID 进行分组。您需要的是带有 SELECT TOP 1 的有序子查询。

select * 
from Customers 
    inner join Orders 
        on Customers.CustomerID = Orders.CustomerID
        and OrderID = (
            SELECT TOP 1 subOrders.OrderID 
            FROM Orders subOrders 
            WHERE subOrders.CustomerID = Orders.CustomerID 
            ORDER BY subOrders.OrderDate DESC
        )

回答by Patrick Harrington

While I see that you've already accepted an answer, I think this one is a bit more intuitive:

虽然我看到您已经接受了一个答案，但我认为这个答案更直观：

select      a.*
           ,b.Id

from       customer a

inner join Order b
on         b.CustomerID = a.Id

where      b.EntryTime = ( select max(EntryTime)
                           from   Order
                           where  Id = b.Id
                         );

I would have to run something like this through an execution plan to see the difference in execution, but where the TOP function is done after-the-fact and that using "order by" can be expensive, I believe that using max(EntryTime) would be the best way to run this.

我将不得不通过执行计划运行这样的事情以查看执行的差异，但是在事后完成 TOP 函数并且使用“order by”可能很昂贵，我相信使用 max(EntryTime)将是运行它的最佳方式。

回答by xahtep

Something like this should do it:

像这样的事情应该这样做：

SELECT X.*, Y.LatestOrderId
FROM Customer X
LEFT JOIN (
  SELECT A.Customer, MAX(A.OrderID) LatestOrderId
  FROM Order A
  JOIN (
    SELECT Customer, MAX(EntryTime) MaxEntryTime FROM Order GROUP BY Customer
  ) B ON A.Customer = B.Customer AND A.EntryTime = B.MaxEntryTime
  GROUP BY Customer
) Y ON X.Customer = Y.Customer

This assumes that two orders for the same customer may have the same EntryTime, which is why MAX(OrderID)is used in subquery Yto ensure that it only occurs once per customer. The LEFT JOINis used because you stated you wanted to show all customers - if they haven't got any orders, then the LatestOrderId will be NULL.

这假设同一客户的两个订单可能具有相同的 EntryTime，这就是为什么MAX(OrderID)在子查询中使用Y它以确保每个客户只发生一次。使用LEFT JOIN是因为您声明要向所有客户显示 - 如果他们没有任何订单，则 LatexOrderId 将为NULL。

Hope this helps!

希望这可以帮助！

--

——

UPDATE :-) This shows only customers with orders:

更新 :-) 这仅显示有订单的客户：

SELECT A.Customer, MAX(A.OrderID) LatestOrderId
FROM Order A
JOIN (
  SELECT Customer, MAX(EntryTime) MaxEntryTime FROM Order GROUP BY Customer
) B ON A.Customer = B.Customer AND A.EntryTime = B.MaxEntryTime
GROUP BY Customer

回答by user34850

You can use a window function.

您可以使用窗口函数。

SELECT *
  FROM (SELECT a.*, b.*,
               ROW_NUMBER () OVER (PARTITION BY a.ID ORDER BY b.orderdate DESC,
                b.ID DESC) rn
          FROM customer a, ORDER b
         WHERE a.ID = b.custid)
 WHERE rn = 1

For each customer (a.id) it sorts all orders and discards everything but the latest. ORDER BY clause includes both order date and entry id, in case there are multiple orders on the same date.

对于每个客户 (a.id)，它对所有订单进行排序并丢弃除最新订单之外的所有订单。ORDER BY 子句包括订单日期和条目 ID，以防同一日期有多个订单。

Generally, window functions are much faster than any look-ups using MAX() on large number of records.

通常，窗口函数比在大量记录上使用 MAX() 进行任何查找都要快得多。

回答by Tom H

One approach that I haven't seen above yet:

我在上面还没有看到的一种方法：

SELECT
     C.*,
     O1.ID
FROM
     dbo.Customers C
INNER JOIN dbo.Orders O1 ON
     O1.CustomerID = C.ID
LEFT OUTER JOIN dbo.Orders O2 ON
     O2.CustomerID = C.ID AND
     O2.EntryTime > O1.EntryTime
WHERE
     O2.ID IS NULL

This (as well as the other solutions I believe) assumes that no two orders for the same customer can have the exact same entry time. If that's a concern then you would have to make a choice as to what determines which one is the "latest". If that's a concern post a comment and I can expand the query if needed to account for that.

这（以及我相信的其他解决方案）假设同一客户的两个订单不能具有完全相同的输入时间。如果这是一个问题，那么您将不得不选择决定哪个是“最新的”。如果这是一个问题，请发表评论，如果需要，我可以扩展查询以解决这个问题。

The general approach of the query is to find the order for a customer where there is not another order for the same customer with a later date. It is then the latest order by definition. This approach often gives better performance then the use of derived tables or subqueries.

查询的一般方法是查找某个客户的订单，其中没有同一客户的另一个日期较晚的订单。根据定义，它是最新的订单。这种方法通常比使用派生表或子查询提供更好的性能。

回答by Saad Achemlal

This query is much faster than the accepted answer :

此查询比接受的答案快得多：

SELECT c.id as customer_id, 
    (SELECT co.id FROM customer_order co WHERE 
    co.customer_id=c.id 
    ORDER BY some_date_column DESC limit 1) as last_order_id
    FROM customer c

回答by Benny Wong

Something like:

就像是：

SELECT
  a.*
FROM
  Customer a
    INNER JOIN Order b
      ON a.OrderID = b.Id
        INNER JOIN (SELECT Id, max(EntryTime) as EntryTime FROM Order b GROUP BY Id) met
          ON
            b.EntryTime = met.EntryTime and b.Id = met.Id

回答by devio

SELECT Cust.*, Ord.*
FROM Customers cust INNER JOIN Orders ord ON cust.ID = ord.CustID
WHERE ord.OrderID = 
    (SELECT MAX(OrderID) FROM Orders WHERE Orders.CustID = cust.ID)