在MySQL中以逗号分隔的行中获取不同值的计数

Question

表格Jobs有2列JobId，保存工作位置时的城市可能是多个城市，如下所示

-----------------------------
JobId               City
-------------------------------
1                   New York
2                   New York , Ohio , Virginia
3                   New York , Virginia

我想如何在类似城市的城市中计算Jobid想要在纽约市中计算Jobid的结果

纽约3俄亥俄州1弗吉尼亚2

Answer 1

SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(all_city, ',', num), ',', -1) AS one_city,
  COUNT(*) AS cnt
FROM (
  SELECT
    GROUP_CONCAT(city separator ',') AS all_city,
    LENGTH(GROUP_CONCAT(citySEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(citySEPARATOR ','), ',', '')) + 1 AS count_city
  FROM table_name
) t
JOIN numbers n
ON n.num <= t.count_city
GROUP BY one_city
ORDER BY cnt DESC;

为了获得用逗号分隔的不同值的计数，可以在查询之上运行，但是要获得正确的结果，您应该再使用一个表**numbers**，该表只有一个列num整数类型，并插入一些值。如果在此情况下在GROUP_CONCAT（城市分隔符’，’）AS
all_city期间出错，则设置一个全局变量“ SET group_concat_max_len = 18446744073709547520;”