SELECt SUBSTRING_INDEX(SUBSTRING_INDEX(all_city, ',', num), ',', -1) AS one_city, COUNT(*) AS cntFROM ( SELECt GROUP_CONCAt(city separator ',') AS all_city, LENGTH(GROUP_CONCAt(citySEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAt(citySEPARATOR ','), ',', '')) + 1 AS count_city FROM table_name) tJOIN numbers nON n.num <= t.count_cityGROUP BY one_cityORDER BY cnt DESC;
为了获得用逗号分隔的不同值的计数，可以在查询之上运行，但是要获得正确的结果，您应该再使用一个表
**numbers**，该表只有一个列num整数类型，并插入一些值。如果在此情况下在GROUP_CONCAT（城市分隔符’，’）AS
all_city期间出错，则设置一个全局变量“ SET group_concat_max_len = 18446744073709547520;”
