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;”
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)