求一个sql语句：以某一列的内容为条件对另一列求和

Question

例：value1 weight
40ab 123
40ac 569
40ab 494
20ab 321
40bc 987
20kl 136
50gb 658
20rt 923
50bg 231
如上：分别查询value1 字段下包含“40”、“20”和“50”的， 然后将各自的结果进行求和。并将求和结果保存成如下：
svalue sweight
20 xxx
40 xxx
50 xxx
敬等各位大侠高见！

Answer 1

SELECT SUBSTR(value1, 1, 2), SUM(weight)
FROM tab
group by SUBSTR(value1, 1, 2)

不同数据库系统需要调整下substring函数追问

有的value1的内容如下：gh40。按上述方法该怎么处理呢

追答

SELECT CASE WHEN VALUE1 LIKE '%40%' THEN '40'
WHEN VALUE1 LIKE '%20%' THEN '20'
WHEN VALUE1 LIKE '%50%' THEN '50'
else null end, SUM(weight)
FROM tab
group by CASE WHEN VALUE1 LIKE '%40%' THEN '40'
WHEN VALUE1 LIKE '%20%' THEN '20'
WHEN VALUE1 LIKE '%50%' THEN '50'
else null end

Answer 2

给一个Oracle 10g以上的版本SQL语句答案：
select regexp_substr(value1,'\d\d'),sum(weight) from 表 group by regexp_substr(value1,'\d\d')

Answer 3

select substr(value1,0,2),sum(weight) from table group by substr(value1,0,2);