如何基于ID和日期从Table 1同步更新Table 2的Value数据

需求说明 #

依据公共列Id，将Table1中按日期排序的Value，按顺序填充到Table2对应Id的空Value行中（Table2的行按日期排序后依次匹配Table1的排序后行）。

原始数据表 #

Table1 #

Table2 #

预期更新后Table2 #

解决方案（SQL实现） #

核心思路是给两个表按Id分组，再按Date排序分配行号，通过Id和行号关联完成更新。

支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server） #

WITH ranked_table1 AS (
    SELECT 
        Id, 
        Value,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Date) AS rn
    FROM Table1
),
ranked_table2 AS (
    SELECT 
        Id, 
        Value,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Date) AS rn
    FROM Table2
    WHERE Value IS NULL -- 仅更新空值行，需覆盖所有行可删除此条件
)
UPDATE ranked_table2 t2
SET t2.Value = t1.Value
FROM ranked_table1 t1
WHERE t2.Id = t1.Id AND t2.rn = t1.rn;

旧版MySQL（不支持CTE） #

-- 生成带行号的Table1临时表
SET @row_num = 0;
SET @current_id = NULL;
CREATE TEMPORARY TABLE temp_table1 AS
SELECT 
    Id, 
    Value,
    @row_num := IF(@current_id = Id, @row_num + 1, 1) AS rn,
    @current_id := Id
FROM Table1
ORDER BY Id, Date;

-- 生成带行号的Table2临时表
SET @row_num = 0;
SET @current_id = NULL;
CREATE TEMPORARY TABLE temp_table2 AS
SELECT 
    Id, 
    Value,
    @row_num := IF(@current_id = Id, @row_num + 1, 1) AS rn,
    @current_id := Id
FROM Table2
WHERE Value IS NULL
ORDER BY Id, Date;

-- 关联更新Table2
UPDATE Table2 t2
JOIN temp_table2 tt2 ON t2.Id = tt2.Id AND t2.Date = tt2.Date
JOIN temp_table1 tt1 ON tt2.Id = tt1.Id AND tt2.rn = tt1.rn
SET t2.Value = tt1.Value;

-- 清理临时表
DROP TEMPORARY TABLE temp_table1;
DROP TEMPORARY TABLE temp_table2;

说明 #

• 方案通过行号匹配实现顺序填充，确保同一Id下，Table2按日期排序的第N行，对应Table1按日期排序的第N行的Value。
• 若Table2行数多于Table1，多出的行Value保持为空；若Table1行数多于Table2，多出的Table1数据不会被使用。

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