如何用单条SQL查询提取多列去重值并合并为单列字符串？

假设你有如下结构和数据的表：

ID  COL1    COL2    COL3
1   P1      d1      V1
2   P1      d2      V1
3   P3      d1      V1
4   P3      d2      V1
5   P4      d1      V1
6   P4      d2      V1

需要提取所有列的去重值，拼接成P1;P3;P4;V1;d1;d2这样的单个字符串，以下是不同数据库的实现方案：

MySQL/MariaDB 写法 #

利用UNION自动去重合并多列数据，再用GROUP_CONCAT完成字符串拼接：

SELECT GROUP_CONCAT(DISTINCT val SEPARATOR ';') AS result
FROM (
    SELECT COL1 AS val FROM your_table
    UNION
    SELECT COL2 AS val FROM your_table
    UNION
    SELECT COL3 AS val FROM your_table
) AS temp;

如果需要按特定顺序拼接，可在GROUP_CONCAT里加上排序规则：

SELECT GROUP_CONCAT(DISTINCT val ORDER BY val SEPARATOR ';') AS result
FROM (
    SELECT COL1 AS val FROM your_table
    UNION
    SELECT COL2 AS val FROM your_table
    UNION
    SELECT COL3 AS val FROM your_table
) AS temp;

SQL Server 写法（2017及以上版本） #

用STRING_AGG函数实现拼接，结合UNION去重：

SELECT STRING_AGG(val, ';') AS result
FROM (
    SELECT COL1 AS val FROM your_table
    UNION
    SELECT COL2 AS val FROM your_table
    UNION
    SELECT COL3 AS val FROM your_table
) AS temp;

也可以先对每列单独去重再合并：

SELECT STRING_AGG(val, ';') AS result
FROM (
    SELECT DISTINCT COL1 AS val FROM your_table
    UNION ALL
    SELECT DISTINCT COL2 AS val FROM your_table
    UNION ALL
    SELECT DISTINCT COL3 AS val FROM your_table
) AS temp;

PostgreSQL 写法 #

用STRING_AGG函数，支持直接指定排序规则：

SELECT STRING_AGG(val, ';' ORDER BY val) AS result
FROM (
    SELECT COL1 AS val FROM your_table
    UNION
    SELECT COL2 AS val FROM your_table
    UNION
    SELECT COL3 AS val FROM your_table
) AS temp;

或者先合并所有值再去重拼接：

SELECT STRING_AGG(DISTINCT val, ';' ORDER BY val) AS result
FROM (
    SELECT COL1 AS val FROM your_table
    UNION ALL
    SELECT COL2 AS val FROM your_table
    UNION ALL
    SELECT COL3 AS val FROM your_table
) AS temp;

注意：将上述代码中的your_table替换为你的实际表名即可。

内容的提问来源于stack exchange，提问作者sanae el madani