MySQL COUNT() 和 FIND_IN_SET()

Question

是否可以对存储为逗号分隔值的数据执行计数？

例如我有两个表:

Items:
=====================
id  name  cats
=====================
1   John  1,2
2   Peter 3
3   Jack  2


Categories:
===========================
id    name    
=========================== 
1     Animals
2     Vegetables
3     Fruits

我想选择所有类别并对“猫”字段进行计数。

结果应该是这样的:

Name          Counter
======================
Animals      1
Vegetables   2
Fruits       1

这是我的查询:

SELECT 
  c.id,
  c.name,
  (SELECT 
    COUNT(i.id) 
  FROM
    Items AS i 
  WHERE FIND_IN_SET(c.id, i.cats)) AS total 
FROM
  categories AS c;

Answer 1

有时，我们无法规范化数据，只需找到一个 SQL 表达式来处理这种情况很快:

select count(*), c.name from items as i,category as c where find_in_set( c.id , i.cats ) !=0 group by c.name;


+----------+------------+
| count(*) | name       |
+----------+------------+
|        1 | Animals    |
|        1 | Fruits     |
|        2 | Vegetables |
+----------+------------+