sql-server - 如何控制计算列的数据类型？

Question

使用 SQL Server 2012...

我有两列:

Price [decimal(28,12)]
OustandingShares [decimal(38,3)] -- The 38 is overkill but alas, not my call.

当我执行 ALTER TABLE 时，我得到一个结果计算列作为 [decimal(38,6)]。我需要数据类型为 [decimal(28,12)]。

ALTER TABLE [xyz].MyTable
ADD Mv AS OustandingShares * Price

如何在此计算列上有效地获得小数点后 12 位？我试过将 OutstandingShares 转换为小数点后 12 位，以及围绕 OutstandingShares * Price 进行转换。我唯一得到的是 [decimal(28,12)] 处的计算字段，其中有六个尾随零。

想法？

Answer 1

修复

这就是你想要的:

CONVERT(DECIMAL(28,12), (
                            CONVERT(DECIMAL(15, 3), [OustandingShares])
                          * CONVERT(DECIMAL(24, 12), [Price])
                         )
       )

用这个测试:

SELECT CONVERT(DECIMAL(28,12),
                 (CONVERT(DECIMAL(24,12), 5304.987781883689)
               * CONVERT(DECIMAL(15,3), 3510.88)));

结果:

18625175.503659806036

原因

由于 SQL Server 关于如何跨各种操作处理精度和缩放的规则，计算正在被截断。这些规则在 Precision, Scale, and Length 的 MSDN 页面中有详细说明。 .我们对这个案例感兴趣的细节是:

• 操作: e1 * e2
• 结果精度: p1 + p2 + 1
• 结果量表*: s1 + s2

这里使用的数据类型是:

• 十进制(28, 12)
• 十进制(38, 3)

这应该导致:

• 精度 = (28 + 38 + 1) = 67
• 规模 = 15

但是 DECIMAL 类型的最大长度是 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.

所以看起来为了将精度降低到 38，它砍掉了小数点后 9 位。

这就是我提出的修复方案有效的原因。我将“Scale”值保持不变，因为我们不想截断并扩展它们没有任何意义，因为 SQL Server 将适本地扩展 Scale。关键在于降低精度，以便截断不存在或至少最小。

使用 DECIMAL(15, 3) 和 DECIMAL(24, 12) 我们应该得到:

• 精度 = (15 + 24 + 1) = 40
• 规模 = 15

40 超出了限制，因此减少 2 以降低到 38，这意味着将比例减少 2，使我们得到真正的“结果比例”13，这比我们需要的多 1，甚至会看到。