SQL 左连接中的最新记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/725153/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Most recent record in a left join
提问by David Glenn
Imagine I have the following 3 tables in SqlServer:
想象一下，我在 SqlServer 中有以下 3 个表：
Customer (CustomerID, FirstName, LastName)
Address (AddressID, CustomerID, Line1, City, State)
Product (ProductID, CustomerID, Description)
A customer can have multiple delivery addresses and mulitple products.
一个客户可以有多个送货地址和多个产品。
What I would like to do is to list the number of customers for each State where the State is determined by the most recent Address record. Such as "How many customers last received a product in each State?". Therefore I'm not interested in any previous Address records for the Customer, only the most Recent (determined by AddressID).
我想做的是列出每个州的客户数量，其中该州由最近的地址记录确定。例如“每个州有多少客户最后收到了产品？”。因此，我对客户以前的任何地址记录不感兴趣，只对最近的（由 AddressID 确定）感兴趣。
State | Number of Customers
--------------------------
CA | 32
GA | 12
TX | 0
OH | 18
I would normally do something like:
我通常会做这样的事情：
SELECT a.State, count(c.CustomerID)
FROM Product p
INNER JOIN Customer c ON c.CustomerID = p.CustomerID
LEFT JOIN Address a ON a.CustomerID = c.CustomerID
WHERE p.ProductID = 101
GROUP BY a.State
However, as a Customer may have multiple Addresses will the customer only be counted in the State of the most recent Address record?
但是，由于客户可能有多个地址，是否仅将客户计入最近地址记录的状态？
P.S. The above is purely an example scenario to easily explain the joins I am trying to achieve and does not reflect an actual system design.
PS 以上纯粹是一个示例场景，用于轻松解释我试图实现的连接，并不反映实际的系统设计。
回答by cjk
Try this:
尝试这个：
SELECT a.State, count(c.CustomerID)
FROM Product p
INNER JOIN Customer c ON c.CustomerID = p.CustomerID
LEFT JOIN Address a ON a.CustomerID = c.CustomerID
AND a.AddressID =
(
SELECT MAX(AddressID)
FROM Address z
WHERE z.CustomerID = a.CustomerID
)
WHERE p.ProductID = 101
GROUP BY a.State
回答by Hank Gay
You could also try (assuming I remember my SQLServer syntax correctly):
您也可以尝试（假设我记得我的 SQLServer 语法正确）：
SELECT state, count(customer_id)
FROM (
SELECT
p.customer_id
, (SELECT TOP 1 State FROM Address WHERE Address.CustomerID = p.CustomerID ORDER BY Address.ID DESC) state
FROM Product p
WHERE p.ProductID = 101)
GROUP BY state
回答by John Saunders
I don't see how you can do this without having Orders and OrderDetails tables. The Orders table would include the CustomerID ShippingDate and ShipToAddressID, and OrderDetails would have the OrderID and ProductID. You'll then need a nested query to determine the most recent order (and hence most recent address), join that to the order details to get the products ordered, then filter on the product you care about.
如果没有 Orders 和 OrderDetails 表，我不知道如何做到这一点。Orders 表将包含 CustomerID ShippingDate 和 ShipToAddressID，OrderDetails 将包含 OrderID 和 ProductID。然后，您需要一个嵌套查询来确定最近的订单（以及最近的地址），将其加入订单详细信息以订购产品，然后过滤您关心的产品。
