如何用纯SQL统计逗号分隔字段中子串的出现次数？

当然可以用纯SQL实现这个需求，不同主流数据库的实现方式略有差异，核心思路都是先把逗号分隔的字符串拆分成独立行，再分组统计每个子串的出现次数。以下是针对常见数据库的具体方案：

场景说明 #

假设你的数据表名为your_table，其中schema列存储逗号分隔的字符串，示例数据如下：

SELECT schema FROM your_table;

输出结果：

schema
a,b,c
b,c
a,c

我们需要得到每个子串的出现次数统计：

label  count
a      2
b      2
c      3

MySQL（8.0及以上版本） #

方法1：递归CTE拆分 #

WITH RECURSIVE split_data AS (
    SELECT 
        SUBSTRING_INDEX(schema, ',', 1) AS label,
        SUBSTRING(schema, LOCATE(',', schema) + 1) AS remaining
    FROM your_table
    WHERE schema != ''
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(remaining, ',', 1) AS label,
        SUBSTRING(remaining, LOCATE(',', remaining) + 1) AS remaining
    FROM split_data
    WHERE remaining != ''
)
SELECT label, COUNT(*) AS count
FROM split_data
GROUP BY label
ORDER BY label;

方法2：JSON_TABLE拆分（更简洁） #

SELECT 
    j.label,
    COUNT(*) AS count
FROM your_table,
     JSON_TABLE(
         CONCAT('["', REPLACE(schema, ',', '","'), '"]'),
         '$[*]' COLUMNS(label VARCHAR(255) PATH '$')
     ) j
GROUP BY j.label
ORDER BY j.label;

PostgreSQL #

PostgreSQL自带的string_to_array+unnest组合可以直接完成拆分：

SELECT 
    unnest(string_to_array(schema, ',')) AS label,
    COUNT(*) AS count
FROM your_table
GROUP BY label
ORDER BY label;

SQL Server（2016及以上版本） #

用STRING_SPLIT函数配合交叉应用实现拆分：

SELECT 
    value AS label,
    COUNT(*) AS count
FROM your_table
CROSS APPLY STRING_SPLIT(schema, ',')
GROUP BY value
ORDER BY value;

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