34,576
社区成员
发帖
与我相关
我的任务
分享
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
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
SELECT SUM(IIF(RIGHT(W,2)=N'千克',CAST(LEFT(W,LEN(W)-2) AS FLOAT)*1.0,
IIF(RIGHT(W,1)=N'吨' , CAST(LEFT(W,LEN(W)-1) AS FLOAT)*1000.0 ,
CAST(LEFT(W,LEN(W)-1) AS FLOAT)/1000
)
)
) AS W
FROM TB
SELECT SUM(SWITCH(RIGHT(W,2)=N'千克' , CONVERT(FLOAT,LEFT(W,LEN(W)-2))*1.0,
RIGHT(W,1)=N'吨' ,CONVERT(FLOAT,LEFT(W,LEN(W)-1))*1000.0 ,
true, CONVERT(FLOAT,LEFT(W,LEN(W)-1))/1000
) AS W
FROM @TB
Iif()函数
Iif()函数用来进行简单条件判断,其语法为:
Iif(expr, truepart, falsepart)
执行时,先判断逻辑表达式expr的值.若值为"True",则返回truepart的值;否则返回falsepart部分的值.