MySQL中基于Emp_ID与Status组合新增Permanent_Status列的实现需求

我有一张包含Emp_ID和Status两列的表，当前数据如下：

需要新增一列Permanent_Status，遵循以下逻辑：

• 若员工拥有Permanent或Temp状态，Permanent_Status设为Y
• 仅当员工只有Casual状态时（注：数据中Emp_ID18的Causal为拼写错误，需视为Casual），该列设为N
• 即使员工同时拥有Permanent/Casual或Temp/Casual组合状态，仍设为Y

期望输出表如下：

解决方案 #

步骤1：新增Permanent_Status列 #

先执行ALTER语句添加目标列（替换your_table_name为实际表名）：

ALTER TABLE your_table_name ADD COLUMN Permanent_Status CHAR(1);

步骤2：批量更新列值 #

提供两种适配不同MySQL版本的实现方式：

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

先通过子查询标记每个员工是否符合Y的条件，再关联原表更新：

UPDATE your_table_name t
JOIN (
    SELECT Emp_ID,
           CASE WHEN EXISTS (
               SELECT 1 FROM your_table_name 
               WHERE Emp_ID = e.Emp_ID 
               AND Status IN ('Permanent', 'Temp')
           ) THEN 'Y' ELSE 'N' END AS status_flag
    FROM (SELECT DISTINCT Emp_ID FROM your_table_name) e
) sub ON t.Emp_ID = sub.Emp_ID
SET t.Permanent_Status = sub.status_flag;

方法2：窗口函数更新（MySQL 8.0及以上版本）

利用窗口函数批量标记每个员工的状态类型，写法更简洁：

UPDATE your_table_name t
JOIN (
    SELECT Emp_ID,
           CASE WHEN MAX(CASE WHEN Status IN ('Permanent', 'Temp') THEN 1 ELSE 0 END) OVER (PARTITION BY Emp_ID) = 1 
                THEN 'Y' ELSE 'N' END AS status_flag
    FROM your_table_name
) sub ON t.Emp_ID = sub.Emp_ID
SET t.Permanent_Status = sub.status_flag;

额外处理：修正拼写错误 #

针对数据中Emp_ID18的Causal拼写错误，可先执行统一修正：

UPDATE your_table_name SET Status = 'Casual' WHERE Status = 'Causal';

可选：自动维护列值的触发器 #

如果需要后续新增数据时自动更新Permanent_Status，可创建触发器：

DELIMITER //
CREATE TRIGGER trg_update_permanent_status
AFTER INSERT ON your_table_name
FOR EACH ROW
BEGIN
    UPDATE your_table_name t
    JOIN (
        SELECT Emp_ID,
               CASE WHEN EXISTS (
                   SELECT 1 FROM your_table_name 
                   WHERE Emp_ID = NEW.Emp_ID 
                   AND Status IN ('Permanent', 'Temp')
               ) THEN 'Y' ELSE 'N' END AS status_flag
        FROM (SELECT DISTINCT Emp_ID FROM your_table_name WHERE Emp_ID = NEW.Emp_ID) e
    ) sub ON t.Emp_ID = sub.Emp_ID
    SET t.Permanent_Status = sub.status_flag;
END //
DELIMITER ;

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