仅联接第二个表中的最后一条记录，但包括第二个表中没有匹配项的记录

Question

我知道有很多类似的问题。实际上，我使用了此功能，但我无法弄清楚如何包括第二个表中没有匹配的记录。

我在MS SQL Server上使用sample Northwind DB。

使用此查询：

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
INNER JOIN
    (
        SELECT CustomerID, MAX(OrderDate) maxDate
        FROM Orders
        GROUP BY CustomerID
    ) b ON Orders.CustomerID = b.CustomerID AND
            Orders.OrderDate = b.maxDate
ORDER BY Orders.OrderDate 

我得到了正确的结果，但是缺少记录，但不匹配。

如果我使用左外连接而代替内在加入：

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
LEFT OUTER JOIN
    (
        SELECT CustomerID, MAX(OrderDate) maxDate
        FROM Orders
        GROUP BY CustomerID
    ) b ON Orders.CustomerID = b.CustomerID AND
            Orders.OrderDate = b.maxDate
ORDER BY Orders.OrderDate 

我缺少记录，但是在这种情况下，我已经重复了客户名称。

我想要：只有他的最后订单获取客户列表，如果他没有订单，他的名字还是应该在场的。

Answer 1

您获得了重复的客户名称，因为您通过订购日期链接。因此，如果您在最后一个日期为某些客户有两个或多个订单，您将获得所有这些最后的订单。如果我假设OrderID具有与订单日期相同的顺序，则以下语句应为每个客户返回一行。

select cs.*, o.* from customers cs
left outer join (
  select customerid, max(orderid) as orderid from orders
  group by customerid
) lnk on cs.customerid = lnk.customerid
left outer join orders o on lnk.orderid = o.orderid
order by cs.customerid

Answer 2

做到这一点的最佳方法是使用ROW_NUMBER()的CTE，此查询的成本将更好，因为您只能击中Orders表，而不是两次获得数据，以获取数据并获得最大记录。

WITH    LastOrder
          AS ( SELECT CustomerID
                   ,OrderID
                   ,OrderDate
                   ,ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY OrderDate DESC ) AS RowNum
                FROM Orders)
    SELECT c.CustomerID
           ,c.CompanyName
           ,lo.OrderID
           ,lo.OrderDate
        FROM Customers AS c
        LEFT OUTER JOIN LastOrder AS lo
            ON c.Customer_id = lo.CustomerID
               AND lo.RowNum = 1

Answer 3

我使用此答案解决了这个问题。

请参阅代码：

select Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
 from Customers
left outer join  Orders ON Customers.CustomerID=Orders.CustomerID
where   Orders.OrderDate is null OR
       Orders.OrderDate = 
       ( SELECT MAX(OrderDate)
           FROM Orders
          WHERE Customers.CustomerID=Orders.CustomerID)       
ORDER BY Customers.CustomerID 

它确实做了我想要的。

更新：最好使用OrderID订购时间：

select Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
 from Customers
left outer join  Orders ON Customers.CustomerID=Orders.CustomerID
where   Orders.OrderID is null OR
       Orders.OrderID = 
       ( SELECT MAX(OrderID)
           FROM Orders
          WHERE Customers.CustomerID=Orders.CustomerID)       
ORDER BY Customers.CustomerID