SQL Server浮点数据类型计算与十进制 [英] SQL Server Float data type calculation vs decimal

问题描述

在下面的查询中：

$ $ p $ code $ declare @a float（23）
declare @b float（23）
declare @c float（53）
set @a = 123456789012.1234
set @b = 1234567.12345678
set @c = @a * @b
select @c

select LTRIM（STR（（@ c），32,12））

declare @x decimal（16,4）
declare @y decimal（16,8 ）
declare @z decimal（32,12）
$ b $ set @x = 123456789012.1234
set @y = 1234567.12345678
set @z = @x * @y
select @z

我得到的答案为

  1.52415693411713E + 17 
 152415693411713020.000000000000 
 152415692881907790.143935926652 
  

从上面的答案第三个答案是正确的。这就是为什么浮点数据类型被称为近似数字数据类型

或者我做了一些根本性的错误。

<顺便说一下，这是由于我有遗留系统，其中我必须使用float作为存储数据类型，同时在计算中不应该有精度损失的问题。

请建议替代方案或解释。

解决方案

浮点数精确到 15有效数字（在SQL Server中）。

1.52415693411713 E + 17 其中 1.52415693411713 （15位数字）和你一样准确。 152415693411713 与 STR 之后的最后 020 ... 是 是浮点的分辨率

为了保持精确度，使用浮动。这很简单。如果你想要计算CAST到CAST，但是如果CAST回到浮动状态， 限制为15位数字 每位计算机科学家应该了解的浮点算术

In the following query

declare @a float(23)
declare @b float(23)
declare @c float(53)
set @a = 123456789012.1234
set @b = 1234567.12345678
set @c = @a * @b
select @c

select LTRIM(STR((@c),32,12))

declare @x  decimal(16,4)
declare @y decimal(16,8)
declare @z decimal (32,12)

set @x = 123456789012.1234
set @y = 1234567.12345678
set @z = @x * @y
select @z

I get answers as

1.52415693411713E+17
152415693411713020.000000000000
152415692881907790.143935926652

From the above answers the third answer is the correct one. Is this the reason why float data type is called Approximate Numeric Data Type

Or am I doing something fundamentally wrong.

BTW this is due to a problem I have with legacy system wherein I have to use float as storage data type, at the same time in there should not be loss of precision while calculation.

Please suggest alternatives, or an explanation.

解决方案

Float is accurate to 15 significant figures only (in SQL Server).

This is demonstrated by 1.52415693411713 E+17 where 1.52415693411713 (15 digits) is as accurate as you'll get. The final 020... after 152415693411713 with STR is made up is the resolution of floating point

To keep precision, don't use float. It is that simple. CAST to decimal if you want for calculation, but if you CAST back to float you are limited to 15 digits

See "What Every Computer Scientist Should Know About Floating-Point Arithmetic"

这篇关于SQL Server浮点数据类型计算与十进制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！