如何在MySQL中处理多列group_concat结果，提取去重非零值并生成唯一列？

问题背景 #

通过GROUP_CONCAT得到如下结果，需要对每列值去重、跳过0值，并新增一列汇总所有唯一值：

原始结果 #

期望结果 #

解决方案 #

方法一：兼容MySQL 5.x版本（使用数字表拆分字符串） #

假设原始数据存储在your_table表中，可通过以下查询实现需求：

WITH split_values AS (
    -- 处理stf_invld_a列：拆分、过滤0、去重
    SELECT DISTINCT 
           SUBSTRING_INDEX(SUBSTRING_INDEX(t.stf_invld_a, ',', n.n), ',', -1) AS val_a,
           NULL AS val_b,
           NULL AS val_c,
           NULL AS val_d
    FROM your_table t
    JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
        ON CHAR_LENGTH(t.stf_invld_a) - CHAR_LENGTH(REPLACE(t.stf_invld_a, ',', '')) >= n.n - 1
    WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(t.stf_invld_a, ',', n.n), ',', -1) != '0'
    
    UNION ALL
    
    -- 处理stf_invld_b列
    SELECT NULL AS val_a,
           SUBSTRING_INDEX(SUBSTRING_INDEX(t.stf_invld_b, ',', n.n), ',', -1) AS val_b,
           NULL AS val_c,
           NULL AS val_d
    FROM your_table t
    JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
        ON CHAR_LENGTH(t.stf_invld_b) - CHAR_LENGTH(REPLACE(t.stf_invld_b, ',', '')) >= n.n - 1
    WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(t.stf_invld_b, ',', n.n), ',', -1) != '0'
    
    UNION ALL
    
    -- 处理stf_invld_c列
    SELECT NULL AS val_a,
           NULL AS val_b,
           SUBSTRING_INDEX(SUBSTRING_INDEX(t.stf_invld_c, ',', n.n), ',', -1) AS val_c,
           NULL AS val_d
    FROM your_table t
    JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
        ON CHAR_LENGTH(t.stf_invld_c) - CHAR_LENGTH(REPLACE(t.stf_invld_c, ',', '')) >= n.n - 1
    WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(t.stf_invld_c, ',', n.n), ',', -1) != '0'
    
    UNION ALL
    
    -- 处理stf_invld_d列
    SELECT NULL AS val_a,
           NULL AS val_b,
           NULL AS val_c,
           SUBSTRING_INDEX(SUBSTRING_INDEX(t.stf_invld_d, ',', n.n), ',', -1) AS val_d
    FROM your_table t
    JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
        ON CHAR_LENGTH(t.stf_invld_d) - CHAR_LENGTH(REPLACE(t.stf_invld_d, ',', '')) >= n.n - 1
    WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(t.stf_invld_d, ',', n.n), ',', -1) != '0'
),
processed_cols AS (
    -- 重新拼接每列的去重后值
    SELECT 
        GROUP_CONCAT(DISTINCT val_a ORDER BY val_a) AS stf_invld_a,
        GROUP_CONCAT(DISTINCT val_b ORDER BY val_b) AS stf_invld_b,
        GROUP_CONCAT(DISTINCT val_c ORDER BY val_c) AS stf_invld_c,
        GROUP_CONCAT(DISTINCT val_d ORDER BY val_d) AS stf_invld_d
    FROM split_values
),
total_unique AS (
    -- 汇总所有唯一值
    SELECT GROUP_CONCAT(DISTINCT val ORDER BY val) AS stf_invld
    FROM (
        SELECT val_a AS val FROM split_values WHERE val_a IS NOT NULL
        UNION ALL
        SELECT val_b AS val FROM split_values WHERE val_b IS NOT NULL
        UNION ALL
        SELECT val_c AS val FROM split_values WHERE val_c IS NOT NULL
        UNION ALL
        SELECT val_d AS val FROM split_values WHERE val_d IS NOT NULL
    ) all_vals
)
-- 合并最终结果
SELECT 
    pc.stf_invld_a,
    pc.stf_invld_b,
    pc.stf_invld_c,
    pc.stf_invld_d,
    tu.stf_invld
FROM processed_cols pc, total_unique tu;

方法二：MySQL 8.0+版本（使用JSON_TABLE简化拆分） #

如果使用MySQL 8.0及以上版本，可借助JSON_TABLE更简洁地拆分逗号分隔字符串：

WITH processed_cols AS (
    SELECT
        -- 处理stf_invld_a列
        (SELECT GROUP_CONCAT(DISTINCT value ORDER BY value)
         FROM JSON_TABLE(CONCAT('["', REPLACE(t.stf_invld_a, ',', '","'), '"]'), 
                        '$[*]' COLUMNS(value VARCHAR(10) PATH '$')) jt
         WHERE value != '0') AS stf_invld_a,
        -- 处理stf_invld_b列
        (SELECT GROUP_CONCAT(DISTINCT value ORDER BY value)
         FROM JSON_TABLE(CONCAT('["', REPLACE(t.stf_invld_b, ',', '","'), '"]'), 
                        '$[*]' COLUMNS(value VARCHAR(10) PATH '$')) jt
         WHERE value != '0') AS stf_invld_b,
        -- 处理stf_invld_c列
        (SELECT GROUP_CONCAT(DISTINCT value ORDER BY value)
         FROM JSON_TABLE(CONCAT('["', REPLACE(t.stf_invld_c, ',', '","'), '"]'), 
                        '$[*]' COLUMNS(value VARCHAR(10) PATH '$')) jt
         WHERE value != '0') AS stf_invld_c,
        -- 处理stf_invld_d列
        (SELECT GROUP_CONCAT(DISTINCT value ORDER BY value)
         FROM JSON_TABLE(CONCAT('["', REPLACE(t.stf_invld_d, ',', '","'), '"]'), 
                        '$[*]' COLUMNS(value VARCHAR(10) PATH '$')) jt
         WHERE value != '0') AS stf_invld_d
    FROM your_table t
),
total_unique AS (
    -- 汇总所有唯一值
    SELECT GROUP_CONCAT(DISTINCT val ORDER BY val) AS stf_invld
    FROM (
        SELECT value AS val FROM your_table, 
               JSON_TABLE(CONCAT('["', REPLACE(stf_invld_a, ',', '","'), '"]'), 
                        '$[*]' COLUMNS(value VARCHAR(10) PATH '$')) jt 
        WHERE value != '0'
        UNION ALL
        SELECT value AS val FROM your_table, 
               JSON_TABLE(CONCAT('["', REPLACE(stf_invld_b, ',', '","'), '"]'), 
                        '$[*]' COLUMNS(value VARCHAR(10) PATH '$')) jt 
        WHERE value != '0'
        UNION ALL
        SELECT value AS val FROM your_table, 
               JSON_TABLE(CONCAT('["', REPLACE(stf_invld_c, ',', '","'), '"]'), 
                        '$[*]' COLUMNS(value VARCHAR(10) PATH '$')) jt 
        WHERE value != '0'
        UNION ALL
        SELECT value AS val FROM your_table, 
               JSON_TABLE(CONCAT('["', REPLACE(stf_invld_d, ',', '","'), '"]'), 
                        '$[*]' COLUMNS(value VARCHAR(10) PATH '$')) jt 
        WHERE value != '0'
    ) all_vals
)
-- 合并最终结果
SELECT 
    pc.stf_invld_a,
    pc.stf_invld_b,
    pc.stf_invld_c,
    pc.stf_invld_d,
    tu.stf_invld
FROM processed_cols pc, total_unique tu;

关键逻辑说明 #

1. 字符串拆分：将逗号分隔的字符串拆分为单行数据，方便过滤和去重；
2. 过滤与去重：通过WHERE value != '0'排除0值，DISTINCT去除重复值；
3. 重新拼接：使用GROUP_CONCAT(DISTINCT ... ORDER BY ...)将处理后的值重新拼接为有序的逗号分隔字符串；
4. 汇总唯一值：通过UNION ALL收集所有列的有效值，再去重拼接为汇总列。

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