如何统计数据库表中逗号分隔列表内各值的出现次数

核心实现逻辑为：先将每行的逗号分隔多值拆分为独立单行数据，再对拆分后的值分组统计计数即可。

MySQL 8.0+ 实现（支持递归CTE） #

WITH RECURSIVE split_result AS (
    SELECT
        -- 拆分第一个值，去除首尾空格
        TRIM(SUBSTRING_INDEX(Column1, ',', 1)) AS val,
        -- 记录拆分后剩余的字符串
        TRIM(SUBSTRING(Column1, LENGTH(SUBSTRING_INDEX(Column1, ',', 1)) + 2)) AS remaining_str
    FROM 你的实际表名
    UNION ALL
    SELECT
        TRIM(SUBSTRING_INDEX(remaining_str, ',', 1)) AS val,
        TRIM(SUBSTRING(remaining_str, LENGTH(SUBSTRING_INDEX(remaining_str, ',', 1)) + 2)) AS remaining_str
    FROM split_result
    WHERE remaining_str != ''
)
SELECT val AS Column1, COUNT(*) AS Count
FROM split_result
GROUP BY val
ORDER BY val;

使用时将你的实际表名替换为你的数据库表真实名称即可。

PostgreSQL 实现 #

PostgreSQL 内置数组拆分和行展开函数，实现更简单：

SELECT 
    TRIM(unnest(string_to_array(Column1, ','))) AS Column1,
    COUNT(*) AS Count
FROM 你的实际表名
GROUP BY Column1
ORDER BY Column1;

SQL Server 实现 #

SELECT 
    TRIM(value) AS Column1,
    COUNT(*) AS Count
FROM 你的实际表名
CROSS APPLY STRING_SPLIT(Column1, ',')
GROUP BY TRIM(value)
ORDER BY TRIM(value);

注：所有实现中使用TRIM是为了清理逗号前后的多余空格，避免ABC 21.17和带前导空格的 ABC 21.17被判定为不同值，匹配你的示例数据场景。

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