TSQL 舍入 VS C# 舍入 [英] TSQL Rounding VS C# Rounding

问题描述

这个问题让我发疯了.我正在使用 Microsoft SQLExpress 2016 编写存储过程.要求之一是进行四舍五入.但时不时地，四舍五入是错误的.我发现 T-SQL 舍入与 C# 并不完全相同，但为什么呢?

This problem has driven me crazy. I am using Microsoft SQLExpress 2016 to write a stored procedure. One of the requirement is to do rounding. But every now and then, the rounding is wrong. I found out that T-SQL rounding is not exactly the same with C#, but why?

比较以下两个四舍五入:

Compare two rounding below:

In T-SQL: ROUND(0.045, 2) --> this will produce 0.05

In C#: Math.Round(0.045, 2) --> this will produce 0.04

为什么 C# 产生 0.04?不应该是0.05吗?

Why C# produces 0.04? Shouldn't it be 0.05?

我该怎么做才能使 C# 舍入 = T-SQL 舍入?有人可以帮我吗?

What should I do so that C# rounding = T-SQL rounding? Can anyone help me?

谢谢，山姆

出于好奇，我在 C# 中尝试了这个:

Out of curiousity, I tried this in C#:

Math.Round(0.055, 2)

猜猜，C# 将其四舍五入为什么?它四舍五入到 0.06！现在，我完全糊涂了！

Guess, what C# rounded it to? It rounded to 0.06! Now, I am completely confused!

Math.Round(0.045, 2)   //this becomes 0.04
Math.Round(0.055, 2)   //this becomes 0.06

谁能解释一下?

谢谢

推荐答案

这是因为 .NET 默认使用ToEven"舍入，而 SQL 使用AwayFromZero".请参阅此.这些是不同的舍入方法，它们在处理 5 的方式上有所不同. AwayFromZero 将其向上舍入到下一个正数，或向下舍入到下一个负数.因此，0.5 变为 1，-0.5 变为 -1.ToEven 舍入到最接近的偶数.所以 2.5 变成 2，3.5 变成 4(对于负数也是如此).5 以外的数字被相同对待，它们被四舍五入到最接近的数字.由于 5 与两个数字等距，因此这是一种特殊情况，具有不同的策略.

This is because .NET defaults to 'ToEven' rounding, while SQL uses 'AwayFromZero'. See This. These are different rounding methods, they differ in how they treat 5. AwayFromZero rounds it up to the next positive, or down to the next negative number. So, 0.5 becomes 1, -0.5 becomes -1. ToEven rounds to the nearest even number. So 2.5 becomes 2, 3.5 becomes 4 (and likewise for negative numbers). Numbers other than 5 are treated the same, they are rounded to the nearest number. Since 5 is equidistant from two numbers, it's a special case, with different strategies.

ToEven 也称为银行规则"，它是 IEEE_754 中使用的默认值，这是为什么它是 .网络.

ToEven is also known as 'Banking Rules', its the default used in IEEE_754, which is why it's the default in .NET.

相反，AwayFromZero 也称为商业四舍五入".我不知道为什么它是 sql server 的默认值，可能只是因为它是最广为人知和理解的方法.

Conversely, AwayFromZero is also known as 'Commercial Rounding'. I don't know why it is the default of sql server, probably simply because it's the most widely known and understood method.

当然，您可以随时配置您需要的:

Of course, you can always configure what you need:

在 C# 中你可以这样做:

In C# you can do:

Math.Round(value, MidpointRounding.ToEven)

或

Math.Round(value, MidpointRounding.AwayFromZero)

在 SQL 中，您可以使用 ROUND()、FLOOR() 和/或 CEILING().

In SQL you can use ROUND(), FLOOR() and/or CEILING().

哪种方法更好，取决于你用它做什么，以及你想要什么.对于合理的集合/分布，四舍五入到偶数值的平均值与其原始值相同.AwayFromZero 不一定是这种情况.如果您有一个包含许多 .5 数据的集合，四舍五入 AwayFromZero 会将所有这些值视为相同，并引入偏差.效果是舍入值的平均值与原始值的平均值不同.四舍五入的目的是使一个值更简单，同时它具有相同的含义.如果平均值不匹配，则情况不再如此；舍入后的值与原始值具有(略有?)不同的含义.

Which of the methods is better, depends what you use it for, and what you want. For reasonable collections/distributions, the average of rounded toEven values is the same as it's original values. This is not necessarily the case with AwayFromZero. If you have a collection with many .5 data, rounding AwayFromZero will treat all those values the same, and introduce a bias. The effect is that the average of the rounded values is not the same as the average of the original values. The point of rounding is making a value simpler, while it holds the same meaning. This is no longer the case if the averages don't match; the rounded values have a (slightly?) different meaning then the original values.

这篇关于TSQL 舍入 VS C# 舍入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！