如何将单条PurchaseDateTime关联多条ServiceDateTime的两张表正确连接？

现有表结构 #

表1（Table1）：购买记录 #

表2（Table2）：服务记录 #

关联需求 #

需将两张表按以下规则关联成新表：

关联规则：

• 每条购买记录关联多条服务记录
• 关联的服务时间需满足：晚于当前购买时间，且早于该客户的下一条购买时间（最后一条购买记录关联所有晚于它的服务记录）
• 所有时间字段无重复值，时间顺序为：PT1 < ST1 < ST2 < ... < STk < PT2 < STk+1... < STk+j < PT3 ...

尝试的SQL语句 #

SELECT 
    t1.CustomerID,
    t1.PurchaseDateTime,
    t2.ServiceDateTime
FROM 
    Table1 t1
JOIN 
    Table2 t2 ON t1.CustomerID = t2.CustomerID
WHERE 
    t2.ServiceDateTime >= t1.PurchaseDateTime
    AND t2.ServiceDateTime <= (
        SELECT MIN(PurchaseDateTime)
        FROM Table1
        WHERE PurchaseDateTime > t1.PurchaseDateTime
        )

该语句存在两个核心问题：

1. 子查询未限定CustomerID，会错误取到其他客户的下一条购买时间，导致关联逻辑混乱
2. 当客户存在最后一条购买记录时，子查询返回NULL，会过滤掉该记录应关联的所有服务

正确的SQL实现 #

方法1：使用窗口函数LEAD（兼容MySQL8+、PostgreSQL、SQL Server等） #

WITH PurchaseWithNext AS (
    SELECT 
        CustomerID,
        PurchaseDateTime,
        -- 获取同一客户的下一条购买时间，无下一条则用极大值兜底
        LEAD(PurchaseDateTime) OVER (PARTITION BY CustomerID ORDER BY PurchaseDateTime) AS NextPurchaseDateTime
    FROM Table1
)
SELECT 
    p.CustomerID,
    p.PurchaseDateTime,
    t2.ServiceDateTime
FROM PurchaseWithNext p
JOIN Table2 t2 
    ON p.CustomerID = t2.CustomerID
    AND t2.ServiceDateTime > p.PurchaseDateTime
    -- 处理最后一条购买记录：无下一条购买时间时，关联所有晚于当前购买时间的服务
    AND (t2.ServiceDateTime < p.NextPurchaseDateTime OR p.NextPurchaseDateTime IS NULL)
ORDER BY 
    p.CustomerID,
    p.PurchaseDateTime,
    t2.ServiceDateTime;

方法2：兼容低版本数据库（无窗口函数） #

-- 关联有下一条购买记录的服务
SELECT 
    t1.CustomerID,
    t1.PurchaseDateTime,
    t2.ServiceDateTime
FROM Table1 t1
JOIN Table2 t2 
    ON t1.CustomerID = t2.CustomerID
    AND t2.ServiceDateTime > t1.PurchaseDateTime
    AND t2.ServiceDateTime < (
        SELECT MIN(PurchaseDateTime)
        FROM Table1
        WHERE CustomerID = t1.CustomerID
          AND PurchaseDateTime > t1.PurchaseDateTime
    )
UNION ALL
-- 关联最后一条购买记录的服务
SELECT 
    t1.CustomerID,
    t1.PurchaseDateTime,
    t2.ServiceDateTime
FROM Table1 t1
JOIN Table2 t2 
    ON t1.CustomerID = t2.CustomerID
    AND t2.ServiceDateTime > t1.PurchaseDateTime
WHERE NOT EXISTS (
    SELECT 1
    FROM Table1
    WHERE CustomerID = t1.CustomerID
      AND PurchaseDateTime > t1.PurchaseDateTime
)
ORDER BY 
    CustomerID,
    PurchaseDateTime,
    ServiceDateTime;

说明 #

• 两种方法都严格限定了同一客户的时间区间关联，避免跨客户逻辑错误
• 方法1用LEAD窗口函数高效获取下一条购买时间，代码更简洁；方法2通过UNION ALL拆分场景，兼容不支持窗口函数的旧版本数据库

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