编写Select SQL实现同表内按指定条件修改type字段输出值

原始数据 #

... |  id    | type | ...
 ... |    1   |  1   | ...
 ... |    1   |  2   | ...
 ... |    1   |  3   | ...
 ... |    1   |  4   | ...
 ... |    2   |  1   | ...
 ... |    2   |  2   | ...
 ... |    3   |  1   | ...
 ... |    3   |  3   | ...
 ... |    4   |  1   | ...
 ... |    5   |  4   | ...

需求 #

编写SELECT语句返回所有行，满足：

• 同一id下同时存在type=3和type=4时，将该id的type=4记录改为3
• 仅存在type=4、无type=3的id，保持type=4不变

错误示例（无法满足需求） #

以下语句会把所有type=4强制改成3，不符合仅当同id存在type=3时才修改的要求：

SELECT id,
CASE WHEN (type = '4')
THEN '3'
ELSE type end AS type
FROM table;

正确查询语句 #

方法1：使用EXISTS子查询 #

通过子查询判断当前id是否存在type=3的记录，再决定是否修改type=4的值：

SELECT 
    id,
    CASE 
        WHEN type = 4 AND EXISTS (
            SELECT 1 
            FROM your_table t2 
            WHERE t2.id = t1.id AND t2.type = 3
        ) 
        THEN 3 
        ELSE type 
    END AS type,
    -- 替换为实际的其他列名，或用t1.*返回所有列
    t1.other_columns
FROM your_table t1;

方法2：使用窗口函数（更高效） #

先通过窗口函数标记每个id是否存在type=3的记录，再进行条件判断，适合大数据量场景：

SELECT 
    id,
    CASE 
        WHEN type = 4 AND has_type_3 = 1 
        THEN 3 
        ELSE type 
    END AS type,
    -- 保留其他列
    other_columns
FROM (
    SELECT 
        *,
        MAX(CASE WHEN type = 3 THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS has_type_3
    FROM your_table
) t;

期望输出结果 #

... |  id    | type | ...
 ... |    1   |  1   | ...
 ... |    1   |  2   | ...
 ... |    1   |  3   | ...
 ... |    1   |  3*  | ...
 ... |    2   |  1   | ...
 ... |    2   |  2   | ...
 ... |    3   |  1   | ...
 ... |    3   |  3   | ...
 ... |    4   |  1   | ...
 ... |    5   |  4   | ...

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