PostgreSQL：基于条件比较并修改同一列中的值

我来帮你搞定这个SQL逻辑的问题——你的需求很清晰，但之前的语句没处理好同一Name下不同Category的对比，咱们一步步修正。

首先得指出你原有SQL的核心问题：
你的CASE条件Category = 'A' and Category = 'B'是永远不会成立的，因为单条记录的Category只能是A或者B，不可能同时满足两个条件，这就是语句没达到预期的根本原因。

接下来，核心需求是对每个Name，对比其A和B类别的my_value，再按规则修改值。要实现这个，我们需要先拿到每个Name对应的A和B的值，这里推荐两种常用方法：

方案1：用窗口函数实现（推荐） #

这个方法不需要额外的表连接，通过窗口函数直接为每条记录获取同一Name下A和B的my_value，然后做判断：

SELECT
    Name,
    Category,
    CASE
        -- 只处理A和B类别，其他类别（C/D/E等）原样保留
        WHEN Category IN ('A', 'B') THEN
            CASE
                -- 先判断当前Name的A和B值是否相等
                WHEN MAX(CASE WHEN Category = 'A' THEN my_value END) OVER (PARTITION BY Name) 
                     = MAX(CASE WHEN Category = 'B' THEN my_value END) OVER (PARTITION BY Name) THEN
                    -- 值相同时：A设为null，B保留原值
                    CASE WHEN Category = 'A' THEN NULL ELSE my_value END
                ELSE
                    -- 值不同时：A保留原值，B设为null
                    CASE WHEN Category = 'A' THEN my_value ELSE NULL END
            END
        ELSE my_value
    END AS Value
FROM my_table
ORDER BY Name, Category;

逻辑拆解：

1. MAX(CASE WHEN Category = 'A' THEN my_value END) OVER (PARTITION BY Name)：为每条记录提取同一Name下A类别的my_value（因为每个Name只有一个A类别，MAX在这里只是用来聚合唯一值）。
2. 同理，第二个窗口函数提取B类别的值。
3. 内层CASE根据A和B的值是否相等，分别对A、B类别设置对应的值；非A/B类别直接返回原my_value。

执行后会完全匹配你期望的输出：

Name Category Value
Ana A 42
Ana B null
Bob A null
Bob B 33
Carla A 42
Carla B null

方案2：用自连接实现 #

如果你对窗口函数不太熟悉，也可以用自连接的方式，把每个Name的A和B值关联到每条记录上：

SELECT
    t.Name,
    t.Category,
    CASE
        WHEN t.Category IN ('A', 'B') THEN
            CASE
                WHEN a.my_value = b.my_value THEN
                    CASE WHEN t.Category = 'A' THEN NULL ELSE t.my_value END
                ELSE
                    CASE WHEN t.Category = 'A' THEN t.my_value ELSE NULL END
            END
        ELSE t.my_value
    END AS Value
FROM my_table t
-- 关联当前Name的A类别记录
LEFT JOIN my_table a ON t.Name = a.Name AND a.Category = 'A'
-- 关联当前Name的B类别记录
LEFT JOIN my_table b ON t.Name = b.Name AND b.Category = 'B'
ORDER BY t.Name, t.Category;

这个方法的逻辑和窗口函数一致，只是通过JOIN来获取A、B的值，适合习惯用连接操作的场景。

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