MySQL如何基于跨行部分匹配规则将指定字段值设为NULL

前置说明 #

请将以下SQL中的employee替换为你实际使用的数据表名。

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

适合需要修改源表内容的场景：

UPDATE employee t1
INNER JOIN (
    -- 筛选出Name、Age、Profession相同但School存在差异的分组
    SELECT Name, Age, Profession
    FROM employee
    GROUP BY Name, Age, Profession
    HAVING COUNT(DISTINCT School) > 1
) t2 
ON t1.Name = t2.Name 
AND t1.Age = t2.Age 
AND t1.Profession = t2.Profession
SET t1.School = NULL;

方案2：仅查询不修改源表 #

适合不需要改动原数据，只需要得到计算结果的场景，用窗口函数实现：

SELECT 
    Name,
    Age,
    Profession,
    Salary,
    CASE 
        WHEN COUNT(DISTINCT School) OVER (PARTITION BY Name, Age, Profession) > 1 
        THEN NULL 
        ELSE School 
    END AS School
FROM employee;

注意事项 #

• 执行更新操作前建议先备份数据，或先单独运行内层子查询确认命中的分组符合预期
• 若School字段存在NULL值，且需要将NULL算作独立的取值参与差异判断，可将COUNT(DISTINCT School)替换为COUNT(DISTINCT IFNULL(School, ''))

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