SQL Server左连接匹配分配日前最新响应记录的实现方法

直接在JOIN ON条件中使用MAX()聚合函数无法执行，本质是因为聚合函数必须依托明确的分组上下文，没有GROUP BY或窗口分区的前提下，数据库无法识别要对哪个范围的数据取最大值。
要实现「单条分配记录仅绑定符合时间规则的最新1条响应」的需求，核心是先完成「按联系人维度筛选出最新响应」的逻辑，再做表关联，以下是两种可直接落地的写法： #

写法1：窗口函数实现（推荐，适配所有支持SQL:2003标准的数据库：SQL Server/PostgreSQL/MySQL 8.0+/BigQuery等） #

逻辑最清晰，性能也更优：

WITH RankedResponse AS (
    SELECT
        CONTACT_ID,
        RESPONSE_ID,
        RESPONSE_DATE,
        CREATED_DATE,
        COALESCE(RESPONSE_DATE, CREATED_DATE) AS RESPONSE_EFFECTIVE_TIME,
        -- 按联系人分区，按响应生效时间倒序编号，最新响应的序号固定为1
        ROW_NUMBER() OVER (
            PARTITION BY CONTACT_ID
            ORDER BY COALESCE(RESPONSE_DATE, CREATED_DATE) DESC, RESPONSE_ID DESC
        ) AS RN
    FROM Response
)
SELECT
    rr.CONTACT_ID,
    rr.RESPONSE_ID,
    rr.RESPONSE_DATE,
    rr.CREATED_DATE,
    d.ASSIGNMENT_DATE AS DISTRIBUTION_DATE
FROM RankedResponse rr
LEFT JOIN Distribution d
    ON rr.CONTACT_ID = d.CONTACT_ID
    -- 保留原12小时宽限期规则
    AND DATEADD(hour, -12, rr.RESPONSE_EFFECTIVE_TIME) <= d.ASSIGNMENT_DATE
-- 仅保留每个联系人下的最新响应参与关联，从根源避免1条分配匹配多条旧响应
WHERE rr.RN = 1;

写法2：子查询聚合实现（适配老版本不支持窗口函数的数据库，如MySQL 5.x） #

通过子查询先算出每个联系人的最新响应时间，再反查对应的响应记录做关联：

SELECT
    resp.CONTACT_ID,
    resp.RESPONSE_ID,
    resp.RESPONSE_DATE,
    resp.CREATED_DATE,
    d.ASSIGNMENT_DATE AS DISTRIBUTION_DATE
FROM Response resp
INNER JOIN (
    SELECT
        CONTACT_ID,
        MAX(COALESCE(RESPONSE_DATE, CREATED_DATE)) AS LATEST_RESP_TIME
    FROM Response
    GROUP BY CONTACT_ID
) latest
    ON resp.CONTACT_ID = latest.CONTACT_ID
    AND COALESCE(resp.RESPONSE_DATE, resp.CREATED_DATE) = latest.LATEST_RESP_TIME
LEFT JOIN Distribution d
    ON resp.CONTACT_ID = d.CONTACT_ID
    AND DATEADD(hour, -12, COALESCE(resp.RESPONSE_DATE, resp.CREATED_DATE)) <= d.ASSIGNMENT_DATE;

注意事项 #

• 原查询出现重复匹配，是因为没有对响应记录做「取最新值」的过滤，所有满足时间不等式的历史响应都会和同CONTACT_ID的分配记录关联，才会出现多条记录携带相同DISTRIBUTION_DATE的问题。
• 如果同一个CONTACT_ID下存在多条响应的COALESCE(RESPONSE_DATE, CREATED_DATE)完全一致，可以在窗口函数的ORDER BY中调整次级排序规则，保证取到的是目标响应记录，避免序号冲突。
• 原查询中的12小时宽限期逻辑完全保留，不需要额外调整。

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