SQL需求：多值显示固定文本，单值保留原字段值

需求 #

当某列对应分组存在多个值时，显示固定值'More than one Value'，否则保留该列的原始值。

现有SQL的问题 #

第一条SQL只能筛选出分组内B.COL3数量大于1的行，没法返回那些单值的记录：

SELECT
    A.COL1,
    A.COL2,
    'More than one Value' AS COL3
FROM TBL1 A
RIGHT JOIN TBL2 B ON A.TBL1-ID = B.TBL2-ID
GROUP BY A.COL1, A.COL2
HAVING COUNT(B.COL3) > 1;

第二条带CASE语句的SQL执行失败，原因是B.COL3既不在GROUP BY子句里，也没被聚合函数包裹，不符合分组查询的规则：

SELECT
    A.COL1,
    A.COL2,
    CASE WHEN COUNT (B.COL3) >1 THEN 'More than one Value' ELSE B.COL3 END AS COL3
FROM TBL1 A
RIGHT OUTER JOIN TBL2 B ON A.TBL1-ID = B.TBL2-ID
GROUP BY A.COL1, A.COL2;

预期结果示例 #

可行解决方案 #

方案一：使用窗口函数 #

窗口函数可以在不压缩行的前提下计算分组内的数量，直接判断后输出：

SELECT DISTINCT
    A.COL1,
    A.COL2,
    CASE 
        WHEN COUNT(B.COL3) OVER (PARTITION BY A.COL1, A.COL2) > 1 
        THEN 'More than one Value' 
        ELSE B.COL3 
    END AS COL3
FROM TBL1 A
RIGHT JOIN TBL2 B ON A.TBL1-ID = B.TBL2-ID;

方案二：子查询统计分组数量后关联 #

先通过子查询算出每个分组的B.COL3数量，再关联原表进行判断：

SELECT
    A.COL1,
    A.COL2,
    CASE 
        WHEN grp.cnt > 1 
        THEN 'More than one Value' 
        ELSE B.COL3 
    END AS COL3
FROM TBL1 A
RIGHT JOIN TBL2 B ON A.TBL1-ID = B.TBL2-ID
JOIN (
    SELECT 
        A.COL1, 
        A.COL2, 
        COUNT(B.COL3) AS cnt
    FROM TBL1 A
    RIGHT JOIN TBL2 B ON A.TBL1-ID = B.TBL2-ID
    GROUP BY A.COL1, A.COL2
) grp ON A.COL1 = grp.COL1 AND A.COL2 = grp.COL2;

关键说明 #

• 窗口函数COUNT(...) OVER (PARTITION BY ...)的优势是不用对结果集进行分组压缩，能保留所有原始行，同时得到分组的统计数。
• 子查询方案则是先完成分组统计，再把统计结果和原表关联，确保每一行都能拿到对应的分组数量进行判断。

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