我在数据库中有一个非常简单的表,有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
…
如果我想按notes字段分组,并进行计数,第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|
+-------------------+------+
这有可能吗?
欢呼。
您可以使用SUBSTRING_INDEX
,如下所示:
SELECT SUBSTRING_INDEX(`notes`, ',', 1), COUNT( `user` ) AS Count
FROM `people`
WHERE `notes` LIKE "%hair%"
GROUP BY SUBSTRING_INDEX(`notes`, ',', 1)
当然,这是一个有点肮脏的解决方法。一个更好的方法是把头发的颜色分开,但我意识到这可能并不总是可行的。
演示