SQL CASE语句处理id_catelog1为空时产生重复id行的问题咨询

解决方法 #

核心逻辑是先统计每个id下对应的不同id_catelog2的数量，再根据统计结果生成id_catelog_final，最后按id去重保证唯一性。

推荐写法（支持窗口函数的数据库通用）

SELECT DISTINCT
    id,
    name,
    id_catelog1,
    Large_id_catelog2,
    CASE
        -- id_catelog1非空时优先取该值
        WHEN id_catelog1 IS NOT NULL THEN id_catelog1
        -- id_catelog1为空且该id对应多个不同id_catelog2时返回multiple
        WHEN COUNT(DISTINCT id_catelog2) OVER (PARTITION BY id) > 1 THEN 'multiple'
        -- 其余情况取唯一的id_catelog2值
        ELSE id_catelog2
    END AS id_catelog_final
FROM
    `table`
WHERE
    id IS NOT NULL;

兼容写法（适配不支持窗口函数DISTINCT统计的数据库）

如果所用数据库不支持在窗口函数中使用DISTINCT，可改用CTE先预统计每个id的id_catelog2数量：

WITH id_catelog_stat AS (
    SELECT 
        id,
        COUNT(DISTINCT id_catelog2) AS catelog2_total
    FROM `table`
    WHERE id IS NOT NULL
    GROUP BY id
)
SELECT DISTINCT
    t.id,
    t.name,
    t.id_catelog1,
    t.Large_id_catelog2,
    CASE
        WHEN t.id_catelog1 IS NOT NULL THEN t.id_catelog1
        WHEN s.catelog2_total > 1 THEN 'multiple'
        ELSE t.id_catelog2
    END AS id_catelog_final
FROM `table` t
INNER JOIN id_catelog_stat s 
    ON t.id = s.id
WHERE t.id IS NOT NULL;

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