t-sql十进制赋值更改值

Why does the select statement below return two different values ?

为什么下面的select语句返回两个不同的值?

declare @tempDec decimal
set @tempDec = 1.0 / (1.0 + 1.0)
select @tempDec, 1.0 / (1.0 + 1.0)

2 个解决方案

#1

That's fine for literals like 1.0, but if you're pulling the data from table columns, you need to cast/convert the first evaluated number in your equation:

对于像1.0这样的文字来说这很好,但如果你从表格列中提取数据,则需要在等式中转换/转换第一个评估数字:

convert(decimal, [col1]) / ([col2] + [col3])

-or-

convert(decimal(15, 2), [col1]) / ([col2] + [col3])

#2

I found out from a coworker just as I posted this.

就像我发布的那样,我从同事那里发现了。

You need to specify the default precision and scale.

您需要指定默认的精度和比例。

This works in this scenario: declare @tempDec decimal(3,2)

这适用于这种情况:声明@tempDec decimal(3,2)

From MSDN:

decimal[ (p[ , s] )] and numeric[ (p[ , s] )] Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

十进制[(p [,s])]和数字[(p [,s])]固定精度和标度数。当使用最大精度时,有效值为 - 10 ^ 38 +1到10 ^ 38 - 1.十进制的SQL-92同义词是dec和dec(p,s)。 numeric在功能上等效于十进制。

p (precision) The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

p(精度)小数点左侧和右侧可存储的最大小数位数。精度必须是从1到最大精度为38的值。默认精度为18。

s (scale) The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

s(scale)可以存储在小数点右侧的最大小数位数。比例必须是从0到p的值。仅在指定精度时才能指定比例。默认比例为0;因此,0 <= s <= p。根据精度,最大存储大小会有所不同。

#1