mysql - 通配符字符串选择 MySQL

Question

我有一张像这样的桌子

|--------------------------------------------|------------------|
|      Column 1                              |     column 2     |
|--------------------------------------------|------------------|
|/fixed/sample_1                             |         10       |
|--------------------------------------------|------------------|
|/fixed/sample_1/                            |         15       |
|--------------------------------------------|------------------|
|/fixed/sample_1/sp1_level2                  |         10       |
|--------------------------------------------|------------------|
|/fixed/sample_1/sp1_level2/sp1_level3       |         20       |
|--------------------------------------------|------------------|
|/fixed/sample_2/                            |         25       |
|--------------------------------------------|------------------|
|/fixed/sample_2/sp2_level2/sp2_level3       |         20       |
|--------------------------------------------|------------------|
|/fixed/sample_3                             |         30       |
|--------------------------------------------|------------------|

我想使用 select 语句从第 1 列中选择并为第 2 列添加值，我的结果表是

|--------------------------------------------|------------------|
|      Column 1                              |     column 2     |
|--------------------------------------------|------------------|
|/fixed/sample_1/                            |         55       |
|--------------------------------------------|------------------|
|/fixed/sample_2/                            |         45       |
|--------------------------------------------|------------------|
|/fixed/sample_3/                            |         30       |
|--------------------------------------------|------------------|

有没有办法做到这一点？

谢谢

标签： mysqlsqlstringselectwildcard

Answer 1

我认为您可以使用substring_index()and group by：

select substring_index(col1, '/', 3) as new_col1,
       sum(col2)
from t
group by new_col1;