mysql - 按部分字符串分组

Question

这是我的表格:

----------+-----------+--------------
45678-sm-w|   18      | Mens clothing
----------+-----------+--------------
45678-sm-b|   5       | Mens clothing
----------+-----------+--------------
2189-L-Wh |   4       | Juniors Clothing
----------+-----------+--------------
2189-L-Bl |   3       | Juniors Clothing
----------+---------- +--------------

这是期望的结果:

----------+-----------+--------------
45678     |   23      | Mens clothing
----------+-----------+--------------
2189      |   7       | Juniors Clothing
----------+-----------+--------------

Answer 1

如果你正在选择数据，那么你可以使用这样的东西:

select SUBSTRING_INDEX(col1, '-', 1) col1, 
  sum(col2) Total, 
  col3
from table1
group by SUBSTRING_INDEX(col1, '-', 1), SUBSTRING_INDEX(col3, ' ', 1)
order by col1 desc

参见 SQL Fiddle With Demo