如何统计数据表中逗号分隔列各条目的出现总次数

核心逻辑为两步：先将每行逗号分隔的reasons字段拆分为「单值占一行」的结构，再对拆分后的结果分组计数即可。以下是不同数据库的具体实现代码：

MySQL 8.0+ 版本 #

WITH RECURSIVE split_reasons AS (
    SELECT
        id,
        reasons AS remaining,
        SUBSTRING_INDEX(reasons, ',', 1) AS reason
    FROM Survey
    UNION ALL
    SELECT
        id,
        SUBSTRING(remaining, LENGTH(reason) + 2) AS remaining,
        SUBSTRING_INDEX(SUBSTRING(remaining, LENGTH(reason) + 2), ',', 1) AS reason
    FROM split_reasons
    WHERE remaining LIKE '%,%'
)
SELECT reason AS reasons, COUNT(*) AS total
FROM split_reasons
GROUP BY reason
ORDER BY total DESC;

如果是MySQL 8.0以下无递归CTE的版本，可借助预先生成的辅助数字表实现拆分，或通过存储过程完成。

PostgreSQL 版本 #

借助内置的数组拆分函数实现，代码更简洁：

SELECT
    unnest(string_to_array(reasons, ',')) AS reasons,
    COUNT(*) AS total
FROM Survey
GROUP BY reasons
ORDER BY total DESC;

SQL Server 2016+ 版本 #

使用内置的STRING_SPLIT函数实现：

SELECT
    value AS reasons,
    COUNT(*) AS total
FROM Survey
CROSS APPLY STRING_SPLIT(reasons, ',')
GROUP BY value
ORDER BY total DESC;

补充说明 #

如果拆分后的值存在前后空格的情况，可以在外层加TRIM()函数处理值，避免a和 a被识别为不同值导致统计错误。另外不建议长期在数据库中存储逗号分隔的多值字段，不符合第一范式，会增加查询、索引、关联的成本，有条件建议拆为独立的关联子表存储。

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