左连接中的最新记录 [英] Most recent record in a left join

问题描述

假设我在 SqlServer 中有以下 3 个表:

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.

我想做的是列出每个州的客户数量，其中该州由最近的地址记录确定.例如每个州有多少客户最后收到了产品?".因此，我对客户以前的任何地址记录不感兴趣，只对最近的(由 AddressID 确定)感兴趣.

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

我通常会这样做:

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.

推荐答案

试试这个:

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

这篇关于左连接中的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！