如何在MySQL中按字符串部分分组 [英] How to GROUP BY a string part in MySQL

问题描述

假设您有下表:

id  string         number
1   stuff::a::312  5 
2   stuff:::a::312 6
3   stuff::a::233  2
4   stuff:b::213   1
5   stuff::b::222  1
6   stuff::c       5

以下内容当然不起作用:

The following doesn't work of course:

SELECT string, COUNT(*)
FROM tbl
-- WHERE
GROUP BY string;

希望的结果:

string numbers
a      13
b      2
c      5

对不起，但是请注意，c后面不是::，就像之前一样

Sorry, but please note that after c is no :: but before, just like the rest

推荐答案

如果模式相同，则可以执行以下操作

If the pattern is same you can do something as

select 
substring_index(string,'::',1) as string_val,
sum(number) as number
from mytable
group by string_val

这篇关于如何在MySQL中按字符串部分分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！