MySQL中统计多列不同文本总数及各文本出现次数

现有数据表如下：

注：类型列是逗号分隔的多标签格式，需要用MySQL完成两个统计需求：

1. 统计所有不同类型标签的总数
2. 统计每个类型标签分别出现在多少条记录中

期望输出示例：

不同类型标签总数 #

各类型标签出现次数 #

MySQL没有直接拆分逗号分隔字符串并统计的内置函数，但可以通过以下两种方法实现需求：

方法一：递归CTE（适用于MySQL 8.0及以上版本） #

1. 统计不同类型标签总数 #

WITH genre_split AS (
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(t.Genre, ',', n.n), ',', -1) AS genre
    FROM 
        你的表名 t
    JOIN 
        (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) n
        ON CHAR_LENGTH(t.Genre) - CHAR_LENGTH(REPLACE(t.Genre, ',', '')) >= n.n - 1
)
SELECT COUNT(DISTINCT genre) AS 不同类型标签总数 FROM genre_split;

说明：(SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3)是生成数字序列，用来拆分多标签，数字数量只需覆盖单条记录中最多的标签数即可，示例中最多3个标签，所以到3就行。

2. 统计每个类型标签的出现次数 #

WITH genre_split AS (
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(t.Genre, ',', n.n), ',', -1) AS genre
    FROM 
        你的表名 t
    JOIN 
        (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) n
        ON CHAR_LENGTH(t.Genre) - CHAR_LENGTH(REPLACE(t.Genre, ',', '')) >= n.n - 1
)
SELECT 
    SUM(CASE WHEN genre = 'Action' THEN 1 ELSE 0 END) AS Action,
    SUM(CASE WHEN genre = 'Adventure' THEN 1 ELSE 0 END) AS Adventure,
    SUM(CASE WHEN genre = 'Sci-Fi' THEN 1 ELSE 0 END) AS Sci-Fi,
    SUM(CASE WHEN genre = 'Mystery' THEN 1 ELSE 0 END) AS Mystery,
    SUM(CASE WHEN genre = 'Horror' THEN 1 ELSE 0 END) AS Horror,
    SUM(CASE WHEN genre = 'Thriller' THEN 1 ELSE 0 END) AS Thriller,
    SUM(CASE WHEN genre = 'Animation' THEN 1 ELSE 0 END) AS Animation,
    SUM(CASE WHEN genre = 'Comedy' THEN 1 ELSE 0 END) AS Comedy,
    SUM(CASE WHEN genre = 'Family' THEN 1 ELSE 0 END) AS Family,
    SUM(CASE WHEN genre = 'Fantasy' THEN 1 ELSE 0 END) AS Fantasy
FROM genre_split;

如果不想手动列举所有类型标签，可以用存储过程动态生成SQL，但静态写法更直接易懂。

方法二：数字辅助表（兼容MySQL 5.x等低版本） #

如果你的MySQL版本不支持CTE，可先创建一个数字辅助表：

-- 创建数字表，生成1到10的数字，可根据实际标签数量扩展
CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

-- 统计不同类型标签总数
SELECT COUNT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(t.Genre, ',', n.n), ',', -1)) AS 不同类型标签总数
FROM 你的表名 t
JOIN numbers n ON CHAR_LENGTH(t.Genre) - CHAR_LENGTH(REPLACE(t.Genre, ',', '')) >= n.n - 1;

-- 统计每个类型标签出现次数
SELECT 
    SUM(CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(t.Genre, ',', n.n), ',', -1) = 'Action' THEN 1 ELSE 0 END) AS Action,
    SUM(CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(t.Genre, ',', n.n), ',', -1) = 'Adventure' THEN 1 ELSE 0 END) AS Adventure,
    -- 按同样格式添加其他类型标签的统计
    SUM(CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(t.Genre, ',', n.n), ',', -1) = 'Fantasy' THEN 1 ELSE 0 END) AS Fantasy
FROM 你的表名 t
JOIN numbers n ON CHAR_LENGTH(t.Genre) - CHAR_LENGTH(REPLACE(t.Genre, ',', '')) >= n.n - 1;

注意：将上述SQL中的你的表名替换为实际使用的表名称。

内容的提问来源于stack exchange，提问作者vie.luo