如何在MySQL/PostgreSQL中统计逗号分隔字段的各值出现次数？

MySQL 实现 #

适用于MySQL 8.0及以上版本 #

利用内置的STRING_SPLIT函数拆分字符串，结合CROSS JOIN展开行，最后去重统计：

SELECT 
    TRIM(s.value) AS emotion,
    COUNT(*) AS count
FROM 
    feedback
CROSS JOIN 
    STRING_SPLIT(emotions, ',') s
GROUP BY 
    TRIM(s.value)
ORDER BY 
    count DESC;

注：TRIM用于去除拆分后字符串的前后空格，避免因空格导致的重复统计（比如示例中的" happy"和"happy"会被识别为同一值）。

适用于MySQL 5.x版本（无STRING_SPLIT） #

使用递归CTE逐段拆分字符串：

WITH RECURSIVE split_emotions AS (
    SELECT 
        1 AS pos,
        SUBSTRING_INDEX(emotions, ',', 1) AS emotion,
        SUBSTRING(emotions, LENGTH(SUBSTRING_INDEX(emotions, ',', 1)) + 2) AS remaining
    FROM feedback
    WHERE emotions IS NOT NULL AND emotions != ''
    UNION ALL
    SELECT 
        pos + 1,
        SUBSTRING_INDEX(remaining, ',', 1),
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2)
    FROM split_emotions
    WHERE remaining IS NOT NULL AND remaining != ''
)
SELECT 
    TRIM(emotion) AS emotion,
    COUNT(*) AS count
FROM split_emotions
GROUP BY 
    TRIM(emotion)
ORDER BY 
    count DESC;

PostgreSQL 实现 #

通用写法（兼容所有支持unnest的版本） #

通过string_to_array将字符串转为数组，再用unnest展开为行：

SELECT 
    TRIM(s.emotion) AS emotion,
    COUNT(*) AS count
FROM feedback
CROSS JOIN 
    unnest(string_to_array(emotions, ',')) AS s(emotion)
GROUP BY 
    TRIM(s.emotion)
ORDER BY 
    count DESC;

适用于PostgreSQL 14及以上版本 #

使用更简洁的string_to_table函数直接拆分字符串为行：

SELECT 
    TRIM(s.emotion) AS emotion,
    COUNT(*) AS count
FROM feedback
CROSS JOIN 
    string_to_table(emotions, ',') AS s(emotion)
GROUP BY 
    TRIM(s.emotion)
ORDER BY 
    count DESC;

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