如何在MySQL查询中统计多值行？

刚好之前处理过类似的需求，给你两种实用的方法来搞定MySQL里多值列的元素统计问题，针对你说的col1列值为a,b、a,c的场景，最终能得到a计数2，b和c各计数1的结果。

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

MySQL 8.0开始支持递归CTE，这是最简洁的方法，不需要额外创建辅助表。

首先先准备测试数据（你可以直接用自己的表替换）：

CREATE TABLE test_table (col1 VARCHAR(50));
INSERT INTO test_table VALUES ('a,b'), ('a,c');

然后执行下面的查询语句，就能拆分元素并统计：

WITH RECURSIVE split_values AS (
    -- 初始步骤：提取每行的第一个元素和剩余未拆分的部分
    SELECT 
        TRIM(SUBSTRING_INDEX(col1, ',', 1)) AS element,
        SUBSTRING(col1, LOCATE(',', col1) + 1) AS remaining
    FROM test_table
    WHERE col1 IS NOT NULL AND col1 != ''
    
    UNION ALL
    
    -- 递归步骤：持续拆分剩余部分，直到没有剩余内容
    SELECT 
        TRIM(SUBSTRING_INDEX(remaining, ',', 1)) AS element,
        SUBSTRING(remaining, LOCATE(',', remaining) + 1) AS remaining
    FROM split_values
    WHERE remaining IS NOT NULL AND remaining != ''
)
-- 分组统计每个元素的出现次数
SELECT element, COUNT(*) AS count
FROM split_values
GROUP BY element
ORDER BY count DESC;

说明： #

• TRIM()函数是为了处理可能存在的空格（比如如果你的值是a, b这种带空格的情况），避免把b和 b当成不同元素统计。
• 递归CTE会把每个多值行拆分成单独的元素行，最后通过GROUP BY和COUNT()就能得到每个元素的频次。

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

如果你的MySQL版本低于8.0，不支持递归CTE，可以用数字辅助表的方法来实现：

首先创建一个临时数字表，里面的数字数量要大于等于你col1列中最多的元素个数（这里假设最多10个，你可以根据实际情况调整）：

CREATE TEMPORARY TABLE numbers (n INT);
INSERT INTO numbers VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

然后执行统计查询：

SELECT 
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.col1, ',', n.n), ',', -1)) AS element,
    COUNT(*) AS count
FROM test_table t
JOIN numbers n ON n.n <= LENGTH(t.col1) - LENGTH(REPLACE(t.col1, ',', '')) + 1
GROUP BY element
ORDER BY count DESC;

说明： #

• LENGTH(t.col1) - LENGTH(REPLACE(t.col1, ',', '')) + 1这个表达式是计算每行col1里的元素个数（逗号数量+1），用来和数字表做关联，确保只拆分出实际存在的元素。
• 同样用TRIM()处理可能的空格问题，保证统计的准确性。

这两种方法都能完美实现你要的多值元素单独统计的需求，根据你的MySQL版本选对应的方法就行～

内容的提问来源于stack exchange，提问作者lil-wolf