mysql - 返回 SQL LIKE & GROUP BY 结果

Question

我在数据库中有一个非常简单的表，其中包含 3 列，名为 people。示例:

user           id             notes
john           01             has red hair, last logged in 02/04/12
tony           02             has brown hair, last logged in 04/03/12
brian          03             has brown hair, last logged in 03/06/13
amanda         04             has blonde hair, last logged in 05/07/14
…

如果我想按注释字段分组并进行计数，则第 2 行和第 3 行显示为每个计数 1，因为登录日期不同；我想做的是砍掉日期并合并并纯粹按头发颜色计算，例如如果我运行查询:

SELECT `notes`, COUNT( `user` ) AS Count 
FROM `people` 
WHERE `notes` LIKE "%hair%" GROUP BY `notes`;

我得到的结果是:

+-----------------------------------------+-------+
|notes                                    | Count |
+-----------------------------------------+-------+
|has red hair, last logged in 02/04/12    |      1|
|has brown hair, last logged in 04/03/12  |      1|
|has brown hair, last logged in 03/06/13  |      1|
|has blonde hair, last logged in 05/07/14 |      1|
+-----------------------------------------+-------+

想要达到的结果:

+-------------------+------+
|notes              |Count | 
+-------------------+------+
|has red hair       |     1|
|has brown hair     |     2|
|has blonde hair    |     1|
+-------------------+------+

这有可能吗？

干杯。

Answer 1

您可以使用 SUBSTRING_INDEX 来实现，如下所示:

SELECT SUBSTRING_INDEX(`notes`, ',', 1), COUNT( `user` ) AS Count
FROM `people`
WHERE `notes` LIKE "%hair%"
GROUP BY SUBSTRING_INDEX(`notes`, ',', 1)

当然这是一个有点肮脏的解决方法。更好的方法是分离头发的颜色，但我意识到这可能并不总是可行。

Demo