mysql - 如何在 MySQL 中按字符串部分分组

Question

编辑:

假设你有下表:

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

以下当然不起作用:

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

希望的结果:

string numbers
a      13
b      2
c      5

抱歉，但请注意 c 之后不是::但之前，就像其余的一样

Answer 1

如果模式相同，你可以做一些事情

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