DECLARE @TB TABLE([W] NVARCHAR(5)) INSERT @TB SELECT N'60克' UNION ALL SELECT N'345千克' UNION ALL SELECT N'0.94吨'SELECT SUM(CASE WHEN RIGHT(W,2)=N'千克' THEN CAST(LEFT(W,LEN(W)-2) AS FLOAT)*1.0 WHEN RIGHT(W,1)=N'吨' THEN CAST(LEFT(W,LEN(W)-1) AS FLOAT)*1000.0 ELSE CAST(LEFT(W,LEN(W)-1) AS FLOAT)/1000 END) AS W FROM @TB
Access 數據庫不能用case when 語句,要用 iff()語句替換.
--> 测试数据: [a] if object_id('[a]') is not null drop table [a] create table [a] (zl varchar(7)) insert into [a] select '60克' union all select '345千克' union all select '0.94吨'select sum(zl) from (select zl=substring(zl,1,patindex('%[千克吨]%',zl)-1)*case substring(zl,patindex('%[千克吨]%',zl),len(zl)) when '克' then 0.0001 when '千克' then 1 when '吨' then 1000 end from [a])a
SELECT sum(aa*iif(bb='克',0.001,iif(bb='千克',1,1000))) as [sum] FROM ( SELECT val(a) AS aa,REPLACE(REPLACE(a,trim(val(a)),''),'0','') AS bb FROM 表 )aa--result /* sum 1285.06 */
DECLARE @TB TABLE([W] NVARCHAR(5)) INSERT @TB SELECT N'60克' UNION ALL SELECT N'345千克' UNION ALL SELECT N'0.94吨'SELECT SUM(CASE WHEN RIGHT(W,2)=N'千克' THEN CAST(LEFT(W,LEN(W)-2) AS FLOAT)*1.0 WHEN RIGHT(W,1)=N'吨' THEN CAST(LEFT(W,LEN(W)-1) AS FLOAT)*1000.0 ELSE CAST(LEFT(W,LEN(W)-1) AS FLOAT)/1000 END) AS W FROM @TB 1楼正解,先统一单位再求和
1楼的OK。DECLARE @TB TABLE([W] NVARCHAR(5)) INSERT @TB SELECT N'60克' UNION ALL SELECT N'345千克' UNION ALL SELECT N'0.94吨'SELECT SUM(CASE WHEN RIGHT(W,2)=N'千克' THEN CAST(LEFT(W,LEN(W)-2) AS FLOAT)*1.0 WHEN RIGHT(W,1)=N'吨' THEN CAST(LEFT(W,LEN(W)-1) AS FLOAT)*1000.0 ELSE CAST(LEFT(W,LEN(W)-1) AS FLOAT)/1000 END) AS W FROM @TB 但是建议楼主写一个函数,专门用作单位转换,以后也可以单独用的。 比如: create function dbo.ConvertUnit(@input1 varchar(20), @input2 varchar(20)) ........调用 select ('千克','吨')
SELECT SUM(CASE WHEN RIGHT(W,2)=N'千克' THEN CAST(LEFT(W,LEN(W)-2) AS FLOAT)*1.0 WHEN RIGHT(W,1)=N'吨' THEN CAST(LEFT(W,LEN(W)-1) AS FLOAT)*1000.0 ELSE CAST(LEFT(W,LEN(W)-1) AS FLOAT)/1000 END) AS W FROM @TB
INSERT @TB
SELECT N'60克' UNION ALL
SELECT N'345千克' UNION ALL
SELECT N'0.94吨'SELECT SUM(CASE WHEN RIGHT(W,2)=N'千克' THEN CAST(LEFT(W,LEN(W)-2) AS FLOAT)*1.0
WHEN RIGHT(W,1)=N'吨' THEN CAST(LEFT(W,LEN(W)-1) AS FLOAT)*1000.0
ELSE CAST(LEFT(W,LEN(W)-1) AS FLOAT)/1000
END) AS W
FROM @TB
if object_id('[a]') is not null drop table [a]
create table [a] (zl varchar(7))
insert into [a]
select '60克' union all
select '345千克' union all
select '0.94吨'select sum(zl) from
(select zl=substring(zl,1,patindex('%[千克吨]%',zl)-1)*case substring(zl,patindex('%[千克吨]%',zl),len(zl)) when '克' then 0.0001 when '千克' then 1 when '吨' then 1000 end
from [a])a
(
SELECT val(a) AS aa,REPLACE(REPLACE(a,trim(val(a)),''),'0','') AS bb FROM 表
)aa--result
/*
sum
1285.06
*/
INSERT @TB
SELECT N'60克' UNION ALL
SELECT N'345千克' UNION ALL
SELECT N'0.94吨'SELECT SUM(CASE WHEN RIGHT(W,2)=N'千克' THEN CAST(LEFT(W,LEN(W)-2) AS FLOAT)*1.0
WHEN RIGHT(W,1)=N'吨' THEN CAST(LEFT(W,LEN(W)-1) AS FLOAT)*1000.0
ELSE CAST(LEFT(W,LEN(W)-1) AS FLOAT)/1000
END) AS W
FROM @TB
1楼正解,先统一单位再求和
INSERT @TB
SELECT N'60克' UNION ALL
SELECT N'345千克' UNION ALL
SELECT N'0.94吨'SELECT SUM(CASE WHEN RIGHT(W,2)=N'千克' THEN CAST(LEFT(W,LEN(W)-2) AS FLOAT)*1.0
WHEN RIGHT(W,1)=N'吨' THEN CAST(LEFT(W,LEN(W)-1) AS FLOAT)*1000.0
ELSE CAST(LEFT(W,LEN(W)-1) AS FLOAT)/1000
END) AS W
FROM @TB 但是建议楼主写一个函数,专门用作单位转换,以后也可以单独用的。
比如: create function dbo.ConvertUnit(@input1 varchar(20), @input2 varchar(20))
........调用
select ('千克','吨')
WHEN RIGHT(W,1)=N'吨' THEN CAST(LEFT(W,LEN(W)-1) AS FLOAT)*1000.0
ELSE CAST(LEFT(W,LEN(W)-1) AS FLOAT)/1000
END) AS W
FROM @TB