求助：日期无效时关联下一个可用日期的SQL JOIN实现

看起来你需要在Customer和Location表之间实现一种特殊的关联逻辑：当转移日期没有对应的Location生效记录时，自动匹配到下一个可用的有效日期（这里的"下一个"可以是转移日期之前最近的，或者之后最早的，我会都覆盖到）。结合你给出的表结构，我整理了两种实用的SQL解决方案：

首先先补全一下必要的表结构和测试数据（因为你没给出Location表的定义，我假设它存储了每个地点在不同时间的地址详情，包含生效日期字段）：

完整表结构与测试数据 #

-- Customer表（你提供的结构）
CREATE TABLE [dbo].[Customer]( 
    [ID] [int] NULL, 
    [Date of Transfer] [datetime] NULL, 
    [Old Location] [int] NULL, 
    [New Location] [int] NULL
);

INSERT INTO [dbo].[Customer] (ID, [Date of Transfer], [Old Location], [New Location])
VALUES 
    (1, '2016-07-01 00:00:00.000', 1001, 2200),
    (1, '2017-11-25 00:00:00.000', 2200, 3300),
    (2, '2018-03-10 00:00:00.000', 4400, 5500);

-- Location表（假设的结构，存储不同时间点的地址详情）
CREATE TABLE [dbo].[Location] (
    [LocationID] [int] NOT NULL,
    [EffectiveDate] [datetime] NOT NULL,
    [Address] [nvarchar(255)] NULL,
    -- 可添加其他地址字段：城市、邮编等
    PRIMARY KEY (LocationID, EffectiveDate)
);

-- 测试用Location数据
INSERT INTO [dbo].[Location] (LocationID, EffectiveDate, Address)
VALUES 
    (1001, '2015-01-01', '123 Old St, City A'),
    (2200, '2016-06-01', '456 New Rd, City B'),
    (2200, '2017-10-01', '789 Updated Ave, City B'),
    (3300, '2018-01-01', '321 Main St, City C'),
    (5500, '2018-04-01', '654 Side Ln, City D');

解决方案1：用CROSS APPLY直接匹配最近可用记录 #

这种方法逻辑直观，性能高效（适合有合适索引的场景），可以精准为每条转移记录匹配到符合条件的第一个Location：

场景A：匹配转移日期之前或当天的最近有效记录 #

如果"下一个可用日期"指的是转移日期当天没有记录时，取之前最近的生效地址：

SELECT 
    c.ID,
    c.[Date of Transfer],
    c.[Old Location],
    c.[New Location],
    -- 旧地点的地址详情
    old_loc.Address AS Old_Location_Address,
    old_loc.EffectiveDate AS Old_Location_Effective_Date,
    -- 新地点的地址详情
    new_loc.Address AS New_Location_Address,
    new_loc.EffectiveDate AS New_Location_Effective_Date
FROM [dbo].[Customer] c
-- 关联旧地点的最近可用记录
CROSS APPLY (
    SELECT TOP 1 l.Address, l.EffectiveDate
    FROM [dbo].[Location] l
    WHERE l.LocationID = c.[Old Location]
      AND l.EffectiveDate <= c.[Date of Transfer]
    ORDER BY l.EffectiveDate DESC -- 倒序取最近的
) old_loc
-- 关联新地点的最近可用记录
CROSS APPLY (
    SELECT TOP 1 l.Address, l.EffectiveDate
    FROM [dbo].[Location] l
    WHERE l.LocationID = c.[New Location]
      AND l.EffectiveDate <= c.[Date of Transfer]
    ORDER BY l.EffectiveDate DESC
) new_loc;

场景B：匹配转移日期之后或当天的最早有效记录 #

如果"下一个可用日期"指的是转移日期当天没有记录时，取之后最早的生效地址，只需要调整查询条件和排序方式：

SELECT 
    c.ID,
    c.[Date of Transfer],
    c.[Old Location],
    c.[New Location],
    old_loc.Address AS Old_Location_Address,
    old_loc.EffectiveDate AS Old_Location_Effective_Date,
    new_loc.Address AS New_Location_Address,
    new_loc.EffectiveDate AS New_Location_Effective_Date
FROM [dbo].[Customer] c
CROSS APPLY (
    SELECT TOP 1 l.Address, l.EffectiveDate
    FROM [dbo].[Location] l
    WHERE l.LocationID = c.[Old Location]
      AND l.EffectiveDate >= c.[Date of Transfer]
    ORDER BY l.EffectiveDate ASC -- 正序取最早的
) old_loc
CROSS APPLY (
    SELECT TOP 1 l.Address, l.EffectiveDate
    FROM [dbo].[Location] l
    WHERE l.LocationID = c.[New Location]
      AND l.EffectiveDate >= c.[Date of Transfer]
    ORDER BY l.EffectiveDate ASC
) new_loc;

解决方案2：用窗口函数ROW_NUMBER()分组匹配 #

如果需要更灵活的分组逻辑（比如后续要扩展更多筛选条件），可以用窗口函数先为每个地点的生效记录排序，再关联Customer表：

WITH Ranked_Locations AS (
    SELECT 
        LocationID,
        EffectiveDate,
        Address,
        -- 按地点分组，生效日期倒序排名（最近的排第1）
        ROW_NUMBER() OVER (PARTITION BY LocationID ORDER BY EffectiveDate DESC) AS Record_Rank
    FROM [dbo].[Location]
)
SELECT 
    c.ID,
    c.[Date of Transfer],
    c.[Old Location],
    c.[New Location],
    old_loc.Address AS Old_Location_Address,
    old_loc.EffectiveDate AS Old_Location_Effective_Date,
    new_loc.Address AS New_Location_Address,
    new_loc.EffectiveDate AS New_Location_Effective_Date
FROM [dbo].[Customer] c
JOIN Ranked_Locations old_loc 
    ON old_loc.LocationID = c.[Old Location]
    AND old_loc.EffectiveDate <= c.[Date of Transfer]
    AND old_loc.Record_Rank = 1 -- 取该地点最近的有效记录
JOIN Ranked_Locations new_loc 
    ON new_loc.LocationID = c.[New Location]
    AND new_loc.EffectiveDate <= c.[Date of Transfer]
    AND new_loc.Record_Rank = 1;

性能优化建议 #

为了让这些查询跑得更快，建议在Location表上创建复合索引：

CREATE INDEX IX_Location_LocationID_EffectiveDate 
ON [dbo].[Location] (LocationID, EffectiveDate) 
INCLUDE (Address); -- 包含需要查询的地址字段，避免回表

如果存在某个地点在转移日期前后都没有有效记录的情况，可以把CROSS APPLY改成OUTER APPLY（或者LEFT JOIN），这样即使没有匹配到Location记录，Customer的记录也会被保留。

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