本文介绍了有 5 的 SQL Server 舍入问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

据我所知,当四舍五入数为 5 时,根据数学四舍五入应该如下工作.

As far as i know according to mathematics rounding should work as below when rounding number is 5.

2.435 => 2.44 (Round Up, if rounding to digit(3) is odd number)
2.445 => 2.44 (Round Down, if rounding to digit(4) is even number)

如果求和没问题,

2.435 + 2.445 =  4.88
2.44 + 2.44 = 4.88

我很确定在 .Net 中也有这样的舍入方式.

I'm pretty sure in .Net also rounding works like this.

但在 SQL Server 中,5 总是四舍五入,根据数学计算是不正确的.

But in SQL server, 5 is always rounding up which is not correct according to maths.

SELECT round(2.345, 2)  = 2.35
SELECT round(2.335, 2) => 2.34

这导致四舍五入值的总和存在 1 美分的差异.

this results to 1 cent discrepancies in summation of rounded values.

2.345 + 2.335 = 4.68
2.35 + 2.34 = 4.69 => which is not correct

我已经尝试过使用小数和货币数据类型.

I have tried this with decimal and money data types.

我做错了吗?有没有办法解决这个问题?

Am i doing something wrong? Is there a work around for this?

推荐答案

如果您确实想在 SQL Server 中使用银行家的舍入...

If you do want to use banker's rounding in SQL Server...

CREATE FUNCTION BankersRounding(@value decimal(36,11), @significantDigits INT)
RETURNS MONEY
AS
BEGIN
    -- if value = 12.345 and signficantDigits = 2...

    -- base = 1000
    declare @base int = power(10, @significantDigits + 1)


    -- roundingValue = 12345
    declare @roundingValue decimal(36,11) = floor(abs(@value) * @base)
    -- roundingDigit = 5
    declare @roundingDigit int = @roundingValue % 10

    -- significantValue = 1234
    declare @significantValue decimal(36,11) = floor(@roundingValue / 10)
    -- lastSignificantDigit = 4
    declare @lastSignificantDigit int = @significantValue % 10


    -- awayFromZero = 12.35
    declare @awayFromZero money = (@significantValue + 1) / (@base / 10)
    -- towardsZero = 12.34
    declare @towardsZero money = @significantValue / (@base / 10)

    -- negative values handled slightly different
    if @value < 0
    begin
        -- awayFromZero = -12.35
        set @awayFromZero = ((-1 * @significantValue) - 1) / (@base / 10)
        -- towardsZero = -12.34
        set @towardsZero = (-1 * @significantValue) / (@base / 10)
    end

    -- default to towards zero (i.e. assume thousandths digit is 0-4)
    declare @rv money = @towardsZero
    if @roundingDigit > 5
        set @rv = @awayFromZero  -- 5-9 goes away from 0
    else if @roundingDigit = 5
    begin
        -- 5 goes to nearest even number (towards zero if even, away from zero if odd)
        set @rv = case when @lastSignificantDigit % 2 = 0 then @towardsZero else @awayFromZero end
    end

    return @rv

end

这篇关于有 5 的 SQL Server 舍入问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 01:36
查看更多