PostgreSQL中按地址优先级规则更新表Preference列的实现方法

需求说明 #

现有员工地址数据表，需要按以下规则更新Preference列：

• 优先把Office类型的地址设为默认（Preference值为Y）
• 如果该员工没有Office地址，就选Home类型的设为默认
• 要是Office和Home都没有，就把Others类型的设为默认

最终每个员工只会有一条地址的Preference是Y，其余都是N。

实现方案 #

方法1：窗口函数写法（推荐，逻辑清晰） #

用ROW_NUMBER()给每个员工的地址按优先级排序，只把排第一的那条标记为默认：

WITH ranked_addresses AS (
    SELECT 
        employee_id,
        address_type,
        -- 按Office>Home>Others的顺序排序，优先级高的排号小
        ROW_NUMBER() OVER (
            PARTITION BY employee_id 
            ORDER BY 
                CASE address_type 
                    WHEN 'Office' THEN 1 
                    WHEN 'Home' THEN 2 
                    WHEN 'Others' THEN 3 
                END
        ) AS rank_num
    FROM your_table_name -- 替换成你的实际表名
)
UPDATE your_table_name t
SET preference = CASE 
    WHEN ra.rank_num = 1 THEN 'Y' 
    ELSE 'N' 
END
FROM ranked_addresses ra
WHERE t.employee_id = ra.employee_id 
  AND t.address_type = ra.address_type;

方法2：GROUP BY+子查询写法（符合你提到的GROUP BY/Having思路） #

先找出每个员工优先级最高的地址类型，再批量更新：

-- 先确定每个员工要标记的默认地址类型
WITH preferred_types AS (
    SELECT 
        employee_id,
        CASE 
            -- 先判断有没有Office地址
            WHEN EXISTS (SELECT 1 FROM your_table_name WHERE employee_id = main.employee_id AND address_type = 'Office') THEN 'Office'
            -- 没有Office就看有没有Home
            WHEN EXISTS (SELECT 1 FROM your_table_name WHERE employee_id = main.employee_id AND address_type = 'Home') THEN 'Home'
            -- 都没有就选Others
            ELSE 'Others'
        END AS preferred_type
    FROM your_table_name main
    GROUP BY employee_id
)
-- 执行更新
UPDATE your_table_name t
SET preference = CASE 
    WHEN t.address_type = pt.preferred_type THEN 'Y' 
    ELSE 'N' 
END
FROM preferred_types pt
WHERE t.employee_id = pt.employee_id;

注意事项 #

• 把代码里的your_table_name换成你实际的表名
• 如果员工ID字段不是employee_id，记得同步替换成你表中的对应字段名
• 两种方法都能实现需求，窗口函数的写法更简洁高效，日常使用更推荐

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