sql server语言 - sql十进制类型-精度和比例差异

Question

为了回答这里的另一个问题，我创建了以下数据结构和行:

create table [resource] (Name varchar(16),date datetime, project varchar(16),hours int)
INSERT INTO resource
values ('Andy Sandy', '2013-03-02', 'Enhancements',40)
INSERT INTO resource
values('Fred Jones', '2013-10-02', 'Enhancements',40)

我执行了如下查询:

select 
case when sum(hours) > 0 Then
    CAST(SUM(hours) as DECIMAL(5,2))/40
else 0 end as [hours],
[DATE]
from resource group by date

结果如下:

Hours           Date
1.000000    2013-03-02 00:00:00.000
1.750000    2013-10-02 00:00:00.000

当我将小时转换为小数时，我指定了精度为5和比例为2。我不明白为什么这个数字是这样的。如果我没有指定精度和比例，那么结果是一样的。为什么会这样?

Answer 1

你正在做numeric(5,2)/40。

From Precision, Scale, and Length

+-----------+------------------------------------+---------------------+
| Operation |          Result precision          |   Result scale *    |
+-----------+------------------------------------+---------------------+
| e1 / e2   | p1 - s1 + s2 + max(6, s1 + p2 + 1) | max(6, s1 + p2 + 1) |
+-----------+------------------------------------+---------------------+

将40视为numeric(2,0)，因为这是保留精度和比例的最小十进制表示。

p1=5
s1=2,
p2=2
s2=0

然后将其代入BOL

中的公式中

Precision: 5 - 2 + 0 + max(6, 2 + 2 + 1) = 9
Scale: max(6, 2 + 2 + 1)                 = 6

所以结果是numeric(9,6)。

你也可以从

看到这个

;WITH cte(thing) AS
(
 SELECT CAST(1 as DECIMAL(5,2))/40
)
SELECT thing, 
       sql_variant_property(thing,'basetype') AS basetype,
       sql_variant_property(thing,'precision') AS precision, 
       sql_variant_property(thing,'scale') AS scale, 
       sql_variant_property(thing,'maxlength') AS maxlength
FROM cte

返回

+----------+----------+-----------+-------+-----------+
|  thing   | basetype | precision | scale | maxlength |
+----------+----------+-----------+-------+-----------+
| 0.025000 | decimal  |         9 |     6 |         5 |
+----------+----------+-----------+-------+-----------+

(注:decimal和numeric是同义词)

Answer 2

真奇怪。如果运行sp_describe_first_result_set

sp_describe_first_result_set N'
select 
case when sum(hours) > 0 Then
    CAST(SUM(hours) as DECIMAL(5,2))/40
else 0 end as [hours],
[DATE]
from resource group by date'

您看到返回的hours列被转换为小数(9,6)。

如果您将原始类型转换为，例如DECIMAL(10,6)，它会将其转换为(14,10)。所以你认为它只是增加了4位小数的精度。不!

将你的除数从40.0更改为400.0 -现在它强制转换为(15,11)-它根据除数的精度增加了一个额外的精度级别。

将其改为40.0000(额外3个零)-现在它是(20,15)。这里有一个函数根据原始值和除数来确定精度。

小数点右边每增加一级精度，原强制类型转换就加(2,1)。左边的每一层精度都要在原来的强制类型上加(1,1)。

要将小时列返回为小数(5,2)，只需执行

select 
case when sum(hours) > 0 Then
    CAST(SUM(hours) /40.0 as decimal(5,2))
else 0 end as [hours],
[DATE]
from resource group by date