MySQL：基于邻近feat_sum值填充scores表的NULL score字段

需求规则 #

• 获取所有score为NULL的记录的feat_sum值
• 对每个这类feat_sum，找到**feat_sum差值最小**且score非空的行，将NULL的score替换为该行的score
• 若存在多个行与目标feat_sum差值相同，则选择其中**score较小**的那个
• id为表的主键

初始表数据 #

scores

| id  | feat_sum | score |
| --- | ---      | ---   |
| 1   | 1.234    | 341   |
| 2   | 5.678    | 758   |
| 3   | 2.234    |  NULL |
| 4   | 8.678    |  NULL |

预期更新结果 #

scores

| id  | feat_sum | score |
| --- | ---      | ---   |
| 1   | 1.234    | 341   |
| 2   | 5.678    | 758   |
| 3   | 2.234    | 341   | 
| 4   | 8.678    | 758   |

示例说明 #

id=3的feat_sum为2.234，与id=1的1.234差值为1.0，与id=2的5.678差值为3.444，因此选择更接近的341作为score。

解决方案 #

1. 验证填充结果的查询语句 #

先通过SELECT语句确认填充逻辑是否正确，根据不同数据库调整TOP 1/LIMIT 1：

SELECT 
    s.id,
    s.feat_sum,
    CASE 
        WHEN s.score IS NULL THEN (
            -- 按差值升序、score升序排序，取第一行的score
            SELECT sc.score
            FROM scores sc
            WHERE sc.score IS NOT NULL
            ORDER BY 
                ABS(sc.feat_sum - s.feat_sum) ASC,
                sc.score ASC
            LIMIT 1  -- MySQL/PostgreSQL用这个；SQL Server替换为TOP 1
        )
        ELSE s.score
    END AS score
FROM scores s;

2. 直接更新表的语句 #

MySQL/PostgreSQL版本

UPDATE scores s
SET s.score = (
    SELECT sc.score
    FROM scores sc
    WHERE sc.score IS NOT NULL
    ORDER BY 
        ABS(sc.feat_sum - s.feat_sum) ASC,
        sc.score ASC
    LIMIT 1
)
WHERE s.score IS NULL;

SQL Server版本

UPDATE s
SET s.score = (
    SELECT TOP 1 sc.score
    FROM scores sc
    WHERE sc.score IS NOT NULL
    ORDER BY 
        ABS(sc.feat_sum - s.feat_sum) ASC,
        sc.score ASC
)
FROM scores s
WHERE s.score IS NULL;

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