如何编写MySQL SQL语句实现字符串列的合并去重？

假设你的表名为your_table，要处理的列是column_name，以下是不同SQL方言的实现方案：

MySQL 8.0+ #

通过数字表拆分字符串，去重后合并：

WITH split_data AS (
    SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.column_name, ',', n.n), ',', -1)) AS item
    FROM your_table t
    CROSS JOIN (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        -- 数字数量需大于列中最多的元素个数，示例中最多2个，这里写4足够覆盖
    ) n
    WHERE n.n <= LENGTH(t.column_name) - LENGTH(REPLACE(t.column_name, ',', '')) + 1
)
SELECT GROUP_CONCAT(DISTINCT item ORDER BY item SEPARATOR ', ') AS result
FROM split_data;

PostgreSQL #

利用数组拆分、展开函数实现：

SELECT string_agg(DISTINCT TRIM(item), ', ' ORDER BY item) AS result
FROM your_table,
     unnest(string_to_array(column_name, ', ')) AS item;

SQL Server 2016+ #

使用内置的字符串拆分与聚合函数：

SELECT STRING_AGG(DISTINCT TRIM(value), ', ') WITHIN GROUP (ORDER BY value) AS result
FROM your_table
CROSS APPLY STRING_SPLIT(column_name, ', ');

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