MySQL中如何按family分组填充child列（取sort≥50的值）

核心思路 #

先提取每个family分组中符合sort≥50条件的child值（多数分组仅一行，少数多行可先手动排查处理），再将该值批量填充到对应分组的所有条目中。

步骤1：提取各分组的目标child值 #

若要确保取sort最大的那一行（避免多行时的随机取值），执行：

SELECT family, child
FROM your_table t1
WHERE sort >= 50
AND sort = (SELECT MAX(sort) FROM your_table t2 WHERE t2.family = t1.family);

如果你的场景中每个分组确实只有一行sort≥50，用更简洁的语句即可：

SELECT family, child
FROM your_table
WHERE sort >= 50
GROUP BY family;

步骤2：批量更新原表 #

将提取到的目标值填充到对应分组的所有条目里：

UPDATE your_table t
JOIN (
    -- 替换为上面的子查询语句
    SELECT family, child
    FROM your_table t1
    WHERE sort >= 50
    AND sort = (SELECT MAX(sort) FROM your_table t2 WHERE t2.family = t1.family)
) AS target ON t.family = target.family
SET t.child = target.child;

如果只想更新child为空的行，添加过滤条件：

UPDATE your_table t
JOIN (
    SELECT family, child
    FROM your_table t1
    WHERE sort >= 50
    AND sort = (SELECT MAX(sort) FROM your_table t2 WHERE t2.family = t1.family)
) AS target ON t.family = target.family
SET t.child = target.child
WHERE t.child IS NULL OR t.child = ''; -- 根据实际空值格式调整

步骤3：处理少数多行的情况 #

先排查出存在多条sort≥50记录的分组，手动处理后再执行更新：

SELECT family, COUNT(*) AS over_limit_rows
FROM your_table
WHERE sort >= 50
GROUP BY family
HAVING COUNT(*) > 1;

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