如何编写SQL向Table A插入匹配最近Start_date的合成记录？

现有数据表 #

Table A #

Table B #

需求说明 #

当Table A与Table B的ID和Start_date组合不匹配时，向Table A插入合成记录：

• 新记录的Age和Dept，选取Table A中同ID下与Table B缺失记录Start_date最接近的行对应值
• 新记录的Salary和Start_date直接使用Table B对应记录的字段值
  最终输出需包含原Table A所有记录及新增合成记录。

解决方案SQL（MySQL语法） #

-- 转换日期格式，便于计算差值
WITH date_converted_A AS (
    SELECT 
        ID,
        Age,
        Dept,
        Salary,
        STR_TO_DATE(Start_date, '%d-%m-%Y') AS start_dt
    FROM TableA
),
date_converted_B AS (
    SELECT 
        ID,
        Salary,
        STR_TO_DATE(Start_date, '%d-%m-%Y') AS start_dt
    FROM TableB
),
-- 筛选Table B中未在Table A出现的记录
missing_records AS (
    SELECT 
        b.ID,
        b.Salary,
        b.start_dt
    FROM date_converted_B b
    LEFT JOIN date_converted_A a 
        ON b.ID = a.ID AND b.start_dt = a.start_dt
    WHERE a.ID IS NULL
),
-- 为每个缺失记录匹配Table A中同ID下日期最接近的行
matched_closest AS (
    SELECT 
        m.ID,
        a.Age,
        a.Dept,
        m.Salary,
        m.start_dt,
        ROW_NUMBER() OVER (
            PARTITION BY m.ID, m.start_dt 
            ORDER BY ABS(DATEDIFF(m.start_dt, a.start_dt)) ASC
        ) AS rn
    FROM missing_records m
    JOIN date_converted_A a ON m.ID = a.ID
)
-- 合并原记录与合成记录并输出
SELECT 
    ID,
    Age,
    Dept,
    Salary,
    DATE_FORMAT(start_dt, '%d-%m-%Y') AS Start_date
FROM date_converted_A
UNION ALL
SELECT 
    ID,
    Age,
    Dept,
    Salary,
    DATE_FORMAT(start_dt, '%d-%m-%Y') AS Start_date
FROM matched_closest
WHERE rn = 1
ORDER BY ID, Start_date;

逻辑说明 #

1. 日期转换：将字符串格式的Start_date转为日期类型，确保能正确计算日期差值。
2. 识别缺失记录：通过左连接筛选出Table B中未在Table A存在的（ID, Start_date）组合。
3. 匹配最接近行：对每个缺失记录，关联同ID的Table A所有行，计算日期差的绝对值，用窗口函数按差值排序，取差值最小的第一行。
4. 合并输出：将原Table A记录与合成记录合并，还原日期格式后按ID和日期排序输出。

其他数据库适配提示 #

• Oracle：用TO_DATE(Start_date, 'DD-MM-YYYY')转换日期，ABS(MONTHS_BETWEEN(m.start_dt, a.start_dt))计算差值
• SQL Server：用CONVERT(DATE, Start_date, 105)转换日期，ABS(DATEDIFF(DAY, m.start_dt, a.start_dt))计算差值

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