SQL中的四舍五入到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

/*感觉太长了,无法发表评论*/

圆形(num,sf-1-地板(log10(abs(num)))

• 做这项工作的部分-在数字上使用圆函数作为正常值,但要四舍五入到的长度是计算出来的。
• ABS确保积极
• log10获取数字中大于0的位数
• floor获取小于结果数的最大整数
• 所以总是向下取整并给出一个整数
• SF-1-楼层(…)给出负数

• 因为round(num,-ve num)舍入到小数点的左边

• 只需一次,圆(123.456,-1)和圆(0.00123,4) 返回请求的答案((120,0.0012)

你可以在四舍五入前除以100,然后再乘以100…

我想我已经做到了。

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