如何在SQL中按子组内顺序批量填充Target Field空值？

核心思路 #

每个Group的最后一个Subgroup存储了有效Target Field值，且同Group下所有Subgroup的记录数一致。我们需要给每个Subgroup内的记录按顺序生成行号，通过行号关联同Group最后一个Subgroup对应位置的Target值，完成null值的填充。

通用SQL方案（适用于PostgreSQL、SQL Server、MySQL 8.0+） #

假设你的表名为your_table，执行以下UPDATE语句：

WITH ranked_data AS (
    SELECT 
        *,
        -- 按Subgroup内的Line ID生成顺序行号
        ROW_NUMBER() OVER (PARTITION BY "Group", Subgroup ORDER BY "Line ID") AS row_num,
        -- 获取当前Group的最后一个子分组
        MAX(Subgroup) OVER (PARTITION BY "Group") AS last_subgroup
    FROM your_table
),
target_mapping AS (
    SELECT 
        "Group",
        row_num,
        "Target Field" AS target_value
    FROM ranked_data
    WHERE Subgroup = last_subgroup
)
UPDATE your_table t
SET "Target Field" = tm.target_value
FROM ranked_data rd
JOIN target_mapping tm 
    ON rd."Group" = tm."Group" 
    AND rd.row_num = tm.row_num
WHERE t."Line ID" = rd."Line ID"
AND t."Target Field" IS NULL;

方案说明 #

1. ranked_data CTE：给每条记录生成两个关键值
   • row_num：在当前Group+Subgroup内，按Line ID排序的行号，确保同位置的记录能对应上
   • last_subgroup：当前Group下的最大Subgroup值，即题目中提到的“最后一个Subgroup”
2. target_mapping CTE：提取每个Group最后一个Subgroup中，各序号对应的有效Target值
3. UPDATE关联：通过Line ID匹配原表记录，再通过Group+row_num关联到对应Target值，仅更新Target Field为null的记录

适配MySQL 5.x版本（无CTE支持） #

如果使用不支持CTE的MySQL版本，可以改用子查询关联：

UPDATE your_table t
JOIN (
    SELECT 
        *,
        @row_num := CASE 
            WHEN @current_group = `Group` AND @current_subgroup = Subgroup THEN @row_num + 1
            ELSE 1
        END AS row_num,
        @current_group := `Group`,
        @current_subgroup := Subgroup,
        (SELECT MAX(Subgroup) FROM your_table y WHERE y.`Group` = your_table.`Group`) AS last_subgroup
    FROM your_table, (SELECT @current_group := '', @current_subgroup := '', @row_num := 0) vars
    ORDER BY `Group`, Subgroup, `Line ID`
) rd ON t.`Line ID` = rd.`Line ID`
JOIN (
    SELECT 
        `Group`,
        @t_row_num := CASE 
            WHEN @t_current_group = `Group` AND @t_current_subgroup = Subgroup THEN @t_row_num + 1
            ELSE 1
        END AS row_num,
        `Target Field` AS target_value,
        @t_current_group := `Group`,
        @t_current_subgroup := Subgroup
    FROM your_table, (SELECT @t_current_group := '', @t_current_subgroup := '', @t_row_num := 0) vars
    WHERE Subgroup = (SELECT MAX(Subgroup) FROM your_table y WHERE y.`Group` = your_table.`Group`)
    ORDER BY `Group`, Subgroup, `Line ID`
) tm ON rd.`Group` = tm.`Group` AND rd.row_num = tm.row_num
SET t.`Target Field` = tm.target_value
WHERE t.`Target Field` IS NULL;

关键注意点 #

• 行号的排序依据必须是Subgroup内的Line ID，确保和题目要求的“子组内的顺序”一致
• 依赖MAX(Subgroup)定位最后一个Subgroup，需保证Subgroup在Group内的数值是递增的（符合示例数据规律）
• 仅更新Target Field为null的记录，不会覆盖已有有效值

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