如何改写SQL语句，显式判断名称对应的红/蓝颜色归属

原表结构与数据 #

CREATE TABLE Colors (
    name INT,
    color CHAR(1)
);

INSERT INTO Colors (name, color) VALUES
(1, 'r'),
(1, 'r'),
(1, 'b'),
(2, 'b'),
(2, 'b'),
(2, 'r'),
(3, 'b'),
(3, 'b'),
(4, 'r');

表中数据如下：

name color
    1     r
    1     r
    1     b
    2     b
    2     b
    2     r
    3     b
    3     b
    4     r

期望输出结果 #

需要新增一列new，显示每个name对应的颜色归属，结果如下：

name color     new
    1     r r and b
    1     r r and b
    1     b r and b
    2     b r and b
    2     b r and b
    2     r r and b
    3     b  only b
    3     b  only b
    4     r  only r

原实现代码 #

原代码通过COUNT(DISTINCT)判断颜色种类：

SELECT 
    C.name,
    C.color,
    CASE 
        WHEN COUNT(DISTINCT C1.color) > 1 THEN 'r and b'
        WHEN MAX(C1.color) = 'r' THEN 'only r'
        ELSE 'only b'
    END as new
FROM 
    Colors C
JOIN 
    Colors C1 ON C.name = C1.name
GROUP BY 
    C.name, 
    C.color;

改写后的SQL代码 #

以下改写版本通过**显式检查每个name是否包含红色('r')和蓝色('b')**生成结果，不依赖DISTINCT关键字：

方案1：子查询预计算颜色标记 #

SELECT
    c.name,
    c.color,
    CASE
        WHEN has_r = 1 AND has_b = 1 THEN 'r and b'
        WHEN has_r = 1 THEN 'only r'
        ELSE 'only b'
    END AS new
FROM Colors c
JOIN (
    SELECT
        name,
        -- 标记当前name是否存在红色
        MAX(CASE WHEN color = 'r' THEN 1 ELSE 0 END) AS has_r,
        -- 标记当前name是否存在蓝色
        MAX(CASE WHEN color = 'b' THEN 1 ELSE 0 END) AS has_b
    FROM Colors
    GROUP BY name
) color_flags ON c.name = color_flags.name;

方案2：窗口函数直接计算（无需JOIN） #

SELECT
    name,
    color,
    CASE
        -- 同时存在红、蓝两种颜色
        WHEN SUM(CASE WHEN color = 'r' THEN 1 ELSE 0 END) OVER (PARTITION BY name) > 0
             AND SUM(CASE WHEN color = 'b' THEN 1 ELSE 0 END) OVER (PARTITION BY name) > 0
        THEN 'r and b'
        -- 仅存在红色
        WHEN SUM(CASE WHEN color = 'r' THEN 1 ELSE 0 END) OVER (PARTITION BY name) > 0
        THEN 'only r'
        -- 仅存在蓝色
        ELSE 'only b'
    END AS new
FROM Colors;

改写思路 #

通过CASE表达式配合聚合函数（或窗口函数），直接为每个name标记是否包含红色和蓝色，再根据标记组合生成对应的描述文本，完全显式检查两种颜色的存在状态，避免依赖去重计数的间接判断逻辑。

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