T-SQL 舍入与 C# 舍入 [英] T-SQL rounding vs. C# rounding

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

问题描述

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

I am using Microsoft SQL Server Express 2016 to write a stored procedure. One of the requirements 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 does C# produce 0.04? Shouldn't it be 0.05?

我该怎么做才能使 C# 舍入 = T-SQL 舍入?

What should I do so that C# rounding = T-SQL rounding?

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

Out of curiosity, 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 的方式上有所不同.

This is because .NET defaults to 'ToEven' rounding, while SQL uses 'AwayFromZero'. See this. These are different rounding methods, and they differ in how they treat 5.

AwayFromZero 将其向上舍入到下一个正数,或向下舍入到下一个负数.因此,0.5 变为 1,-0.5 变为 -1.ToEven 舍入到最接近的偶数.所以 2.5 变成 2,3.5 变成 4(对于负数也是如此).除 5 以外的数字被视为相同,并四舍五入到最接近的数字.由于 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, and 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,这是为什么.NET 中的默认设置.

ToEven is also known as 'Banking Rules', and it's 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 its 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.

这篇关于T-SQL 舍入与 C# 舍入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆