我希望能够在SQL中将数字四舍五入到n个有效数字。例如:
123.456 rounded to 2sf would give 120
0.00123 rounded to 2sf would give 0.0012
我知道ROUND()函数,它可以将数字四舍五入到小数点后n位,而不是有效数字。
我希望能够在SQL中将数字四舍五入到n个有效数字。例如:
123.456 rounded to 2sf would give 120
0.00123 rounded to 2sf would give 0.0012
我知道ROUND()函数,它可以将数字四舍五入到小数点后n位,而不是有效数字。
select round(@number,@sf-1- floor(log10(abs(@number))))
可以解决问题!
在你的两个例子上成功测试过。
编辑:对于@number=0,调用此函数将不起作用。您应该在使用此代码之前添加一个测试。
create function sfround(@number float, @sf int) returns float as
begin
declare @r float
select @r = case when @number = 0 then 0 else round(@number ,@sf -1-floor(log10(abs(@number )))) end
return (@r)
end
针对像我一样的人,将Brann最受欢迎的MySQL答案进行了调整。
CREATE FUNCTION `sfround`(num FLOAT, sf INT) # creates the function
RETURNS float # defines output type
DETERMINISTIC # given input, will return same output
BEGIN
DECLARE r FLOAT; # make a variable called r, defined as a float
IF( num IS NULL OR num = 0) THEN # ensure the number exists, and isn't 0
SET r = num; # if it is; leave alone
ELSE
SET r = ROUND(num, sf - 1 - FLOOR(LOG10(ABS(num))));
/* see below*/
END IF;
RETURN (r);
END
/* 这段注释太长了,没法放在注释里 */
ROUND(num, sf - 1 - FLOOR(LOG10(ABS(num))))
之所以有效,是因为ROUND(num,-ve num)将舍入到小数点左侧
对于只需一次舍入的情况,ROUND(123.456,-1)和ROUND(0.00123,4)返回所请求的答案((120、0.0012)
我想我已经成功了。
CREATE FUNCTION RoundSigFig(@Number float, @Figures int)
RETURNS float
AS
BEGIN
DECLARE @Answer float;
SET @Answer = (
SELECT
CASE WHEN intPower IS NULL THEN 0
ELSE FLOOR(fltNumber * POWER(CAST(10 AS float), intPower) + 0.5)
* POWER(CAST(10 AS float), -intPower)
END AS ans
FROM (
SELECT
@Number AS fltNumber,
CASE WHEN @Number > 0
THEN -((CEILING(LOG10(@Number)) - @Figures))
WHEN @Number < 0
THEN -((FLOOR(LOG10(@Number)) - @Figures))
ELSE NULL END AS intPower
) t
);
RETURN @Answer;
END
在舍入之前,您可以将数字除以100,然后再乘以100...
使用格式:
cast(format(@valueToConvert,'E'+cast(@SignificantDigits-1 as varchar(2)),'en-us') as float)
cast(format(@valueToConvert,'E2','en-us') as float)