SQL中使用CTE根据p_id分组按条件批量修改列值为NULL的实现方法

API 探路者

2026-10-2

实现方案

核心思路

先用聚合CTE统计每个p_id分组下各列是否存在NULL值，再将统计结果和原表关联，按规则替换列值即可，全程仅用CTE实现，不需要自定义函数或存储过程。

可直接运行的SQL代码

WITH col_null_flag AS (
    SELECT 
        p_id,
        -- COUNT统计会自动忽略NULL，若某列的统计结果小于分组总行数，说明该列存在NULL
        CASE WHEN COUNT(`1_m_ret`) < COUNT(*) THEN 1 ELSE 0 END AS has_null_1m,
        CASE WHEN COUNT(`2_m_ret`) < COUNT(*) THEN 1 ELSE 0 END AS has_null_2m,
        CASE WHEN COUNT(`3_m_ret`) < COUNT(*) THEN 1 ELSE 0 END AS has_null_3m,
        CASE WHEN COUNT(`4_m_ret`) < COUNT(*) THEN 1 ELSE 0 END AS has_null_4m,
        CASE WHEN COUNT(`5_m_ret`) < COUNT(*) THEN 1 ELSE 0 END AS has_null_5m
    FROM 你的表名
    GROUP BY p_id
)
SELECT 
    t.p_id,
    t.m_id,
    CASE WHEN f.has_null_1m = 1 THEN NULL ELSE t.`1_m_ret` END AS `1_m_ret`,
    CASE WHEN f.has_null_2m = 1 THEN NULL ELSE t.`2_m_ret` END AS `2_m_ret`,
    CASE WHEN f.has_null_3m = 1 THEN NULL ELSE t.`3_m_ret` END AS `3_m_ret`,
    CASE WHEN f.has_null_4m = 1 THEN NULL ELSE t.`4_m_ret` END AS `4_m_ret`,
    CASE WHEN f.has_null_5m = 1 THEN NULL ELSE t.`5_m_ret` END AS `5_m_ret`
FROM 你的表名 t
INNER JOIN col_null_flag f ON t.p_id = f.p_id;