SQL表关联需求：默认按ID+日期关联，遇重复时匹配时间

可以通过窗口函数+分组排序的方式实现你的需求：默认按ID+日期关联，当存在多行匹配时优先保留时间匹配的记录；若只有一行匹配，则直接保留该行。这种方式比在JOIN条件里嵌套CASE WHEN更直观可靠，因为JOIN条件无法动态调整关联逻辑，而窗口函数可以对分组后的行进行优先级筛选。

具体实现代码 #

WITH ranked_matches AS (
    SELECT
        t1.ID1,
        t1.Date1,
        t1.Time1,
        t2.ID2,
        t2.Date2,
        -- 给每个ID+日期分组的行排序：时间匹配的排第一，其余排后面
        ROW_NUMBER() OVER (
            PARTITION BY t1.ID1, t1.Date1
            ORDER BY 
                CASE WHEN t1.Time1 = CAST(t2.Date2 AS TIME) THEN 0 ELSE 1 END,
                t2.Date2 -- 可选：若有多个不匹配的行，按时间先后选最早/最晚的
        ) AS row_rank
    FROM 表1 t1
    LEFT JOIN 表2 t2
        ON t1.ID1 = t2.ID2
        AND CAST(t1.Date1 AS DATE) = CAST(t2.Date2 AS DATE)
)
-- 只保留每个分组里优先级最高的行
SELECT ID1, Date1, Time1, ID2, Date2
FROM ranked_matches
WHERE row_rank = 1;

逻辑说明 #

1. 基础关联：先通过ID1=ID2和日期相等的条件做左连接，确保所有表1的记录都被保留，同时关联表2中同ID同日期的所有记录。
2. 分组排序：用PARTITION BY t1.ID1, t1.Date1把数据按ID和日期分组，每个分组内用ROW_NUMBER()排序：
   • 时间匹配的行（t1.Time1 = CAST(t2.Date2 AS TIME)）会被标记为0，排在分组的最前面；
   • 时间不匹配的行标记为1，排在后面；
   • 若有多个时间不匹配的行，可通过t2.Date2进一步排序（比如选最早的一条）。
3. 筛选结果：最后只取每个分组里row_rank=1的行，也就是每个ID+日期组合下优先级最高的匹配记录。

优化补充 #

如果你的数据库支持简化的日期/时间提取函数（比如MySQL的DATE()/TIME()），可以替换成更高效的写法：

• 日期匹配：DATE(t1.Date1) = DATE(t2.Date2)（若表1的Date1是字符串类型，需确保格式能被正确转换）
• 时间匹配：t1.Time1 = TIME(t2.Date2)

内容的提问来源于stack exchange，提问作者Hasan Burak Ersan