多CASE语句SELECT查询分组输出问题求助：合并条目至单行并去重

嘿，我懂你现在的困扰——用带多个CASE语句的SELECT查询后，结果里每个rolodex条目都拆成了好几行，你想把同一rolodex的所有关联值都合并到一行里，还不能有重复值对吧？这其实是个很常见的字符串聚合需求，不同数据库有对应的解决方案，我给你分情况说明：

首先先假设你的原始查询大概是类似这样的（导致多行输出的原因通常是分组时包含了type或value字段）：

SELECT 
    rolodex_id,
    CASE WHEN type = 'email' THEN value END AS email,
    CASE WHEN type = 'phone' THEN value END AS phone,
    CASE WHEN type = 'address' THEN value END AS address
FROM your_table
GROUP BY rolodex_id, type, value;

接下来是针对不同数据库的优化方案：

1. MySQL/MariaDB 方案 #

用GROUP_CONCAT()函数，搭配DISTINCT去重，轻松合并同一rolodex下的同类型值：

SELECT 
    rolodex_id,
    GROUP_CONCAT(DISTINCT CASE WHEN type = 'email' THEN value END SEPARATOR ', ') AS emails,
    GROUP_CONCAT(DISTINCT CASE WHEN type = 'phone' THEN value END SEPARATOR ', ') AS phones,
    GROUP_CONCAT(DISTINCT CASE WHEN type = 'address' THEN value END SEPARATOR ', ') AS addresses
FROM your_table
GROUP BY rolodex_id;

• DISTINCT确保同类型的重复值不会被多次拼接
• SEPARATOR可以自定义分隔符，比如换成'; '或者' | '都可以

2. PostgreSQL 方案 #

PostgreSQL用STRING_AGG()函数实现同样的效果，语法和逻辑和上面类似：

SELECT 
    rolodex_id,
    STRING_AGG(DISTINCT CASE WHEN type = 'email' THEN value END, ', ') AS emails,
    STRING_AGG(DISTINCT CASE WHEN type = 'phone' THEN value END, ', ') AS phones,
    STRING_AGG(DISTINCT CASE WHEN type = 'address' THEN value END, ', ') AS addresses
FROM your_table
GROUP BY rolodex_id;

3. SQL Server 方案 #

2017及以上版本 #

直接用STRING_AGG()，和PostgreSQL的写法一致：

SELECT 
    rolodex_id,
    STRING_AGG(DISTINCT CASE WHEN type = 'email' THEN value END, ', ') AS emails,
    STRING_AGG(DISTINCT CASE WHEN type = 'phone' THEN value END, ', ') AS phones,
    STRING_AGG(DISTINCT CASE WHEN type = 'address' THEN value END, ', ') AS addresses
FROM your_table
GROUP BY rolodex_id;

2016及更早版本 #

需要用FOR XML PATH的方式来拼接字符串：

SELECT 
    rolodex_id,
    STUFF((
        SELECT DISTINCT ', ' + value 
        FROM your_table t2 
        WHERE t2.rolodex_id = t1.rolodex_id AND t2.type = 'email'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS emails,
    STUFF((
        SELECT DISTINCT ', ' + value 
        FROM your_table t2 
        WHERE t2.rolodex_id = t1.rolodex_id AND t2.type = 'phone'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS phones,
    STUFF((
        SELECT DISTINCT ', ' + value 
        FROM your_table t2 
        WHERE t2.rolodex_id = t1.rolodex_id AND t2.type = 'address'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS addresses
FROM your_table t1
GROUP BY rolodex_id;

• FOR XML PATH('')把查询结果拼接成XML字符串，TYPE保留原始字符避免转义
• STUFF用来去掉开头多余的, （从第1位开始，删除2个字符）

额外小提示 #

• 如果某个rolodex没有对应类型的值，结果列会显示NULL，可以用COALESCE()函数替换成空字符串或默认值，比如COALESCE(GROUP_CONCAT(...), '')
• 确保rolodex_id是分组的唯一标识，这样每个rolodex条目只会出现在一行里

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