如何用SQL实现同一Batch ID下Batch Status值批量填充

原始数据 #

期望结果 #

解决方案 #

方案1：查询时填充（不修改原表） #

如果仅需获取填充后的结果、无需改动原数据，用窗口函数最简洁。利用MAX()忽略NULL的特性，按Batch ID分组后提取该组的非NULL状态值：

SELECT 
    `Batch ID`,
    `Test ID`,
    `Status`,
    MAX(`Batch Status`) OVER (PARTITION BY `Batch ID`) AS `Batch Status`
FROM your_table_name;

也可使用FIRST_VALUE()（部分数据库如PostgreSQL 11+、SQL Server 2022+支持IGNORE NULLS简化逻辑）：

SELECT 
    `Batch ID`,
    `Test ID`,
    `Status`,
    FIRST_VALUE(`Batch Status`) OVER (
        PARTITION BY `Batch ID` 
        ORDER BY CASE WHEN `Batch Status` IS NOT NULL THEN 0 ELSE 1 END
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS `Batch Status`
FROM your_table_name;

方案2：直接更新原表数据 #

若需修改原表，将NULL的Batch Status替换为同批次的非NULL值，可使用关联更新：

MySQL/MariaDB 写法

UPDATE your_table_name t1
JOIN (
    SELECT `Batch ID`, `Batch Status` 
    FROM your_table_name 
    WHERE `Batch Status` IS NOT NULL
) t2 ON t1.`Batch ID` = t2.`Batch ID`
SET t1.`Batch Status` = t2.`Batch Status`
WHERE t1.`Batch Status` IS NULL;

PostgreSQL/SQL Server 写法

UPDATE your_table_name
SET `Batch Status` = (
    SELECT `Batch Status` 
    FROM your_table_name t2 
    WHERE t2.`Batch ID` = your_table_name.`Batch ID` 
      AND t2.`Batch Status` IS NOT NULL
    LIMIT 1
)
WHERE `Batch Status` IS NULL;

注意：上述方案默认每个Batch ID仅存在一个非NULL的Batch Status值，若存在多个，需先明确优先级逻辑（如取最新、状态权重高的值）再调整代码。

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