如何关联客户表与采购日期表并保留无采购记录的客户

我有两张表：

• CustomerTable：存储客户ID与全名
• PurchaseDates：存储客户的每次采购日期

需要查询所有客户的最新采购日期，同时保留尚未有采购记录的客户（其采购日期显示为NULL）。

当前创建的SQL视图仅返回有采购记录的客户（比如CustomerID为1的John Doe，最新采购日期为11/21/2021），但无法显示无采购记录的CustomerID为2的Jane Doe。

现有SQL视图代码 #

SELECT ct.CustomerID,
       ct.Full_Name,
       pd.Purchase_Date,

FROM   CustomerTable AS ct
       LEFT OUTER JOIN PurchaseDates AS pd
                    ON ct.CustomerID = pd.CustomerID
WHERE  EXISTS (SELECT 1 
               FROM   PurchaseDates  AS pd_latest
               WHERE  ( CustomerID= pd.CustomerID)
               GROUP  BY CustomerID
               HAVING ( Max(Purchase_Date) = pd.Purchase_Date)) 

期望查询结果 #

问题出在WHERE EXISTS子句上——它会过滤掉所有没有采购记录的客户，因为这类客户的pd.Purchase_Date是NULL，子查询根本匹配不到。以下两种方法可以解决：

方法一：先聚合采购表再左连接 #

先在PurchaseDates中按客户分组，算出每个客户的最新采购日期，再和CustomerTable做左连接，自然会保留无采购记录的客户：

SELECT ct.CustomerID,
       ct.Full_Name,
       pd_latest.latest_purchase_date AS Purchase_Date
FROM CustomerTable AS ct
LEFT JOIN (
    SELECT CustomerID, MAX(Purchase_Date) AS latest_purchase_date
    FROM PurchaseDates
    GROUP BY CustomerID
) AS pd_latest ON ct.CustomerID = pd_latest.CustomerID

方法二：使用窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

用ROW_NUMBER()窗口函数给每个客户的采购日期按时间倒序排序，取排序后第一条（最新的）记录，再和客户表关联：

SELECT ct.CustomerID,
       ct.Full_Name,
       pd.Purchase_Date
FROM CustomerTable AS ct
LEFT JOIN (
    SELECT 
        CustomerID,
        Purchase_Date,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Purchase_Date DESC) AS rn
    FROM PurchaseDates
) AS pd ON ct.CustomerID = pd.CustomerID AND pd.rn = 1

这两种方法都能保留所有客户，无采购记录的客户Purchase_Date会显示为NULL，完全符合需求。

内容的提问来源于stack exchange，提问作者lamazibiji