Most recent record in a left join

问题:

Imagine I have the following 3 tables in SqlServer:

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).

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.

回答1:

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

回答2:

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.

回答3:

You could also try (assuming I remember my SQLServer syntax correctly):

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