如何按PersonGroupId分组统一更新表中PersonId=2和3的Value字段

需求说明 #

指定firstPersonId = 2，secondPersonId = 3，需在每个同时包含这两个PersonId的PersonGroupId分组内，将两人的Value字段统一为相同值，规则如下：

表结构与测试数据 #

CREATE TABLE `table`(
    id INT,
    PersonGroupId INT,
    PersonId INT,
    Value INT
);

INSERT INTO `table`(id, PersonGroupId, PersonId, Value)
VALUES
    (1, 100, 123, 1),
    (2, 100, 2, NULL),
    (3, 100, 3, 1),
    (4, 101, 2, 1),
    (5, 101, 3, NULL),
    (6, 102, 2, NULL),
    (7, 102, 3, NULL),
    (8, 103, 2, 1),
    (9, 103, 3, 1);

解决方案：UPDATE语句 #

以下是兼容MySQL、PostgreSQL等主流数据库的通用写法：

UPDATE `table` t
JOIN (
    -- 筛选同时包含PersonId=2和3的分组，并计算每个分组的目标Value
    SELECT 
        PersonGroupId,
        MAX(Value) AS target_value -- MAX自动忽略NULL，匹配需求中取值规则
    FROM `table`
    WHERE PersonId IN (2, 3)
    GROUP BY PersonGroupId
    HAVING COUNT(DISTINCT PersonId) = 2 -- 确保分组内同时存在两个指定PersonId
) g ON t.PersonGroupId = g.PersonGroupId
SET t.Value = g.target_value
WHERE t.PersonId IN (2, 3); -- 仅更新指定的两个人

语句逻辑说明 #

1. 子查询g：
   • 筛选出所有关联PersonId=2或3的记录
   • 按PersonGroupId分组，通过HAVING条件确保分组内同时存在两个指定PersonId
   • 用MAX(Value)计算目标值：只要有一个非NULL的1就返回1，都为NULL则返回NULL，完全匹配需求规则
2. 主UPDATE语句：
   • 将原表与子查询结果关联，仅更新目标分组中PersonId为2或3的记录，统一设置为分组的目标值

更新后验证结果 #

执行语句后，表中数据将变为：

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