SQL 语句帮助 - 为每个客户选择最新的订单 [英] SQL Statement Help - Select latest Order for each Customer

问题描述

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

Say I have 2 tables: Customers and Orders. A Customer can have many 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.

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

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

更新:在这里浏览了几个答案(有很多！)后，我意识到我犯了一个错误:我的意思是任何客户的最新记录.这意味着如果他没有订单，那么我不需要列出他.

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

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

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

推荐答案

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

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.

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
        )

这篇关于SQL 语句帮助 - 为每个客户选择最新的订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！