如何对比同一张表中相同ID的行并更新Status字段

核心逻辑：先统计每个ID对应的Value唯一值数量，若数量为1则所有行Value匹配，标记为MATCHED；否则标记为NOT MATCHED。

MySQL 实现 #

方法1：子查询关联更新（兼容所有版本） #

UPDATE your_table t
JOIN (
    SELECT ID, 
           CASE WHEN COUNT(DISTINCT Value) = 1 THEN 'MATCHED' ELSE 'NOT MATCHED' END AS new_status
    FROM your_table
    GROUP BY ID
) AS status_map ON t.ID = status_map.ID
SET t.Status = status_map.new_status;

方法2：窗口函数（MySQL 8.0+） #

UPDATE your_table t
JOIN (
    SELECT ID, Value,
           CASE WHEN COUNT(DISTINCT Value) OVER (PARTITION BY ID) = 1 THEN 'MATCHED' ELSE 'NOT MATCHED' END AS new_status
    FROM your_table
) AS status_map ON t.ID = status_map.ID AND t.Value = status_map.Value
SET t.Status = status_map.new_status;

SQL Server 实现 #

CTE批量更新 #

WITH status_cte AS (
    SELECT ID, Value, Status,
           CASE WHEN COUNT(DISTINCT Value) OVER (PARTITION BY ID) = 1 THEN 'MATCHED' ELSE 'NOT MATCHED' END AS new_status
    FROM your_table
)
UPDATE status_cte
SET Status = new_status;

PostgreSQL 实现 #

CTE关联更新 #

WITH status_cte AS (
    SELECT ID, 
           CASE WHEN COUNT(DISTINCT Value) = 1 THEN 'MATCHED' ELSE 'NOT MATCHED' END AS new_status
    FROM your_table
    GROUP BY ID
)
UPDATE your_table t
SET Status = c.new_status
FROM status_cte c
WHERE t.ID = c.ID;

注意事项 #

• 替换your_table为实际表名
• 若Value包含NULL值，COUNT(DISTINCT Value)会忽略NULL；如需将NULL视为有效匹配，可改用COUNT(DISTINCT COALESCE(Value, 'unique_placeholder'))
• 更新前建议单独运行子查询/CTE部分，验证计算出的new_status是否符合预期

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