Самая последняя запись в левом соединении

Предположим, у меня есть следующие 3 таблицы в SqlServer:

Customer (CustomerID, FirstName, LastName)
Address (AddressID, CustomerID, Line1, City, State)
Product (ProductID, CustomerID, Description)

Клиент может иметь несколько адресов доставки и mulitple продуктов.

Что я хотел бы сделать, так это указать количество клиентов для каждого государства, в котором государство определяется самой последней записью адреса. Например, "Сколько клиентов в последний раз получали продукт в каждом государстве?". Поэтому меня не интересуют какие-либо предыдущие адресные записи для Клиента, только самые последние (определенные AddressID).

State | Number of Customers
--------------------------
CA    | 32
GA    | 12
TX    | 0
OH    | 18

Я бы обычно делал что-то вроде:

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

Однако, поскольку у Клиента может быть несколько Адресов, клиент будет засчитан только в состоянии самой последней записи адреса?

P.S. Вышеприведенное является просто примерным сценарием, позволяющим легко объяснить соединения, которые я пытаюсь достичь, и не отражает фактический дизайн системы.

Ответы

Ответ 1

Попробуйте следующее:

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

Я не вижу, как вы можете это сделать, не имея таблиц Заказы и OrderDetails. В таблице "Заказы" будут указаны CustomerID ShippingDate и ShipToAddressID, а OrderDetails будут иметь идентификатор OrderID и ProductID. Затем вам понадобится вложенный запрос, чтобы определить самый последний заказ (и, следовательно, самый последний адрес), присоедините его к деталям заказа, чтобы получить упорядоченные продукты, а затем отфильтруйте интересующий вас продукт.

Ответ 3

Вы также можете попробовать (при условии, что я правильно помню синтаксис 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