MySQL中如何统计多行逗号分隔数字串的数字出现次数并取Top5？

开发者阿泽

2026-8-4

如何用MySQL统计逗号分隔列中数字的出现次数并取前5？

当然可以实现，以下是针对不同MySQL版本的解决方案：

MySQL 8.0及以上版本（支持递归CTE）

假设你的表名为your_table，存储逗号分隔数字的列是style_ids，可以用递归CTE拆分字符串后统计：

WITH RECURSIVE split_numbers AS (
    SELECT 
        SUBSTRING_INDEX(style_ids, ',', 1) AS number,
        SUBSTRING(style_ids, LOCATE(',', style_ids) + 1) AS remaining
    FROM your_table
    WHERE style_ids IS NOT NULL AND style_ids != ''
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(remaining, ',', 1) AS number,
        SUBSTRING(remaining, LOCATE(',', remaining) + 1) AS remaining
    FROM split_numbers
    WHERE remaining IS NOT NULL AND remaining != ''
)
SELECT 
    CAST(number AS UNSIGNED) AS number,
    COUNT(*) AS appearance_count_in_all_rows
FROM split_numbers
GROUP BY number
ORDER BY appearance_count_in_all_rows DESC, number ASC
LIMIT 5;

逻辑说明

递归CTE split_numbers 会逐行把style_ids中的逗号分隔数字拆分成单独的行
对拆分后的数字分组，用COUNT(*)统计每个数字的出现次数
按出现次数降序排序，次数相同时按数字升序排列，最后取前5个结果

MySQL 8.0以下版本（不支持CTE）

可以借助临时数字表来实现拆分：

首先创建临时数字表（数字数量要大于你表中每行最多的数字个数）：

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

然后执行统计查询：

SELECT 
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(t.style_ids, ',', n.n), ',', -1) AS UNSIGNED) AS number,
    COUNT(*) AS appearance_count_in_all_rows
FROM your_table t
JOIN numbers n ON n.n <= LENGTH(t.style_ids) - LENGTH(REPLACE(t.style_ids, ',', '')) + 1
GROUP BY number
ORDER BY appearance_count_in_all_rows DESC, number ASC
LIMIT 5;