34,590
社区成员
发帖
与我相关
我的任务
分享
select sum(SUBSTRING(字段,1,patindex('%[^0-9]%',字段)-1))
from 表名 group by SUBSTRING(字段,patindex('%[^0-9]%',字段),LEN(字段))
--不过尽量设计表时还是将数据和单位分开。否则后期处理起来效率高,难度也增大了。
create table tb (col varchar(100))
insert into tb values ('50g')
insert into tb values ('40g')
insert into tb values ('60ml')
select
sum(cast(left(col,patindex('%[^0-9]%',col)-1) as int)),
right(col,LEN(col)-patindex('%[^0-9]%',col)+1)
from tb
group by right(col,LEN(col)-patindex('%[^0-9]%',col)+1)
----------- ----------------------------------------------------------------------------------------------------
90 g
60 ml
(2 行受影响)
use tempdb;
/*
create table t2
(
[id] nvarchar(10) not null
);
insert into t2(id)
values
('25ml'),
('50ml'),
('75ml'),
('50g'),
('25g');
*/
select SUM(id1) as [ml的总和],SUM(id2) as [g的总和]
from
(
select
case when id like '%ml' then CAST(left(id,LEN(id)-2) as int) end as [id1],
case when id like '%g' then CAST(left(id,LEN(id)-1) as int) end as [id2]
from t2
) as t;
select sum(cast(left(字段,PATINDEX('%[^0-9]%',字段)-1) as int))
from tb group by right(字段,len(字段)-PATINDEX('%[^0-9]%',字段))
select sum(left(字段,PATINDEX('%[^0-9]%',字段)-1))
from tb group by right(字段,len(字段)-PATINDEX('%[^0-9]%',字段))