SQL Server在视图中截断新创建字段的小数点 [英] SQL Server truncates decimal points of a newly created field in a view

问题描述

我在SQL Server中有一个视图，像这样:

I have a view in SQL server, something like this:

select 6.71/3.41 as NewNumber

结果为1.967741(注意6个小数点)-> decimal (38,6)

The result is 1.967741 (note 6 decimal points) -> decimal (38,6)

我在计算器中尝试相同的操作，但结果为1.967741935483871xxxx

I try the same thing in a calculator but the result is 1.967741935483871xxxx

我想强制SQL Server返回更准确的结果，例如decimal(38,16) 我已经尝试了一些明显的事情，例如强制转换，但是SQL Server并没有改善输出，我只是在结尾处得到一些尾随零，例如1.9677410000

I want to force SQL Server to return more accurate result something like decimal(38,16) I have tried the obvious things like casting, but SQL Server doesn't improve the output I just get some trailing zeros at the end like 1.9677410000

是否有一种方法可以强制SQL Server不截断结果或给出更准确的结果?

Is there a way to force SQL Server to not truncate the result or give more accurate one?

推荐答案

如果您想要类似decimal(38,16)的内容，则需要在截断发生后强制转换输入而不是输出！

If you want something like decimal(38,16) then you need to cast the inputs not the output after truncation has already occurred!

SELECT CAST(6.71 AS DECIMAL(38,18))/3.41 AS NewNumber

返回

1.9677419354838709

检查数据类型

SELECT 
SQL_VARIANT_PROPERTY(CAST(6.71 AS DECIMAL(38,18))/3.41, 'BaseType'),
SQL_VARIANT_PROPERTY(CAST(6.71 AS DECIMAL(38,18))/3.41, 'Precision'),
SQL_VARIANT_PROPERTY(CAST(6.71 AS DECIMAL(38,18))/3.41, 'Scale')

返回

numeric 38  16

编辑

这只是为了添加附加链接，以跟进评论.在BOL中描述了decimal到decimal转换的规则 .该链接包含以下短语

Edit

This is just to add an additional link as follow up to the comments. The rules for decimal to decimal conversion are described in BOL. That link includes the following phrase

*结果精度和小数位数的绝对最大值为38. 结果精度大于38， 相应的比例缩小为 防止结果不可或缺的一部分 被截断.

*The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

，但未明确说明截断的执行方式. 在此处记录.

but leaves it unspecified exactly how such truncation is performed. This is documented here.

这篇关于SQL Server在视图中截断新创建字段的小数点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！