mysql 查询逗号分割的字符串中出现最多次数的字符串并且排序

在日常开发中，我们常常会遇到一些数据处理的需求，比如需要分析某个字段中包含的逗号分割字符串，找出出现次数最多的字符串。在本篇文章中，我们将为刚入行的小白详细讲解如何在MySQL中实现这个目标。整个过程可分为以下几个步骤：

字段值示例：

1,2,5

2,3,4

1,3

4,5

$tag_sql = "SELECT fruit, COUNT(*) AS count FROM ( SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', numbers.n), ',', -1)) AS fruit FROM mh_dealer INNER JOIN ( SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 ) numbers ON CHAR_LENGTH(name) - CHAR_LENGTH(REPLACE(name, ',', '')) >= numbers.n - 1 ) AS subquery GROUP BY fruit ORDER BY count DESC LIMIT 3"; $res = $dealer->query($tag_sql);

name 为查询分组的字段名

LIMIT 统计1-10 的分组数据