加入第二个表中的最后一个记录，但包含第二个表中没有匹配的记录

I know there are a lot similar questions. Actually I used this and it works, but I can't figure out how to include records, which don't have match in second table.

我知道有很多类似的问题。实际上我使用它并且它有效,但我无法弄清楚如何包含第二个表中没有匹配的记录。

I use sample Northwind db on MS SQL Server.

我在MS SQL Server上使用示例Northwind数据库。

Using this Query:

使用此查询:

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 

I get correct result, but missing records, which don't match.

我得到了正确的结果,但丢失的记录不匹配。

If I use LEFT OUTER JOIN instead INNER JOIN:

如果我使用LEFT OUTER JOIN而不是INNER JOIN:

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 

I get missing records, but in this case I have repeated customer names.

我丢失了记录,但在这种情况下,我重复了客户名称。

I want: get list of customers with only his last order and if he doesn't have order his name should be present anyway.

我想:获得只有他最后一个订单的客户名单,如果他没有订单,他的名字应该存在。

3 个解决方案

#1

You got repeated customer names, because you link over orderdate. So if you have two or more orders on the last date for some customer you get all these last orders. If I assume that the orderid has the same sequence as the orderdate, following statement should return only one line for each customer.

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

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

#2

Best way to do this would be CTE with ROW_NUMBER() this query will have better cost because you hit Orders table only once instead of twice to get the data and once to get max record.

最好的方法是CTE和ROW_NUMBER()这个查询会有更好的成本,因为你只需要一次点击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

#3

I used this answer to resolve this problem.

我用这个答案来解决这个问题。

See the code:

看代码:

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 

It did exactly what I want.

它完全符合我的要求。

UPDATE: It's better to use OrderID instead OrderTime:

更新:最好使用OrderID而不是OrderTime:

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