SQL 如何使用正确的小数/$ 结果转换计算列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7802209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to cast computed column with correct decimal/$ result
提问by genxgeek
I have the following computed column in sql server 2008
我在 sql server 2008 中有以下计算列
[Total] AS CAST ( ((val1/(1000)) * [val2]) AS DECIMAL(18,2)) PERSISTED,
When val1 = 862500 and val2 = 8, the computed value = 6896.00
当 val1 = 862500 和 val2 = 8 时，计算值 = 6896.00
I need it to be decimal/money where (862500/1000) * 8 = 6900.00 (not 6896.00).
我需要它是十进制/金钱，其中 (862500/1000) * 8 = 6900.00（不是 6896.00）。
回答by Alex_L
BOL says:
BOL 说：
Caution: When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.
Therefore, similar expressions in queries can sometimes produce different results. When a query is not autoparameterized, the constant value is first converted to numeric, whose precision is just large enough to hold the value of the constant, before converting to the specified data type. For example, the constant value 1 is converted to numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).
When a query is autoparameterized, the constant value is always converted to numeric (10, 0) before converting to the final data type. When the / operator is involved, not only can the result type's precision differ among similar queries, but the result value can differ also. For example, the result value of an autoparameterized query that includes the expression SELECT CAST (1.0 / 7 AS float) will differ from the result value of the same query that is not autoparameterized, because the results of the autoparameterized query will be truncated to fit into the numeric (10, 0) data type. For more information about parameterized queries, see Simple Parameterization.
注意：当您使用 +、-、*、/ 或 % 算术运算符将 int、smallint、tinyint 或 bigint 常量值隐式或显式转换为 float、real、decimal 或 numeric 数据类型时，应遵循以下规则： SQL Server 在计算表达式结果的数据类型和精度时适用，具体取决于查询是否自动参数化。
因此，查询中的相似表达式有时会产生不同的结果。当查询未自动参数化时，常量值首先转换为数字，其精度刚好足以容纳常量的值，然后再转换为指定的数据类型。例如，常量值 1 转换为数字 (1, 0)，常量值 250 转换为数字 (3, 0)。
当查询被自动参数化时，常量值总是在转换为最终数据类型之前转换为数字 (10, 0)。当涉及到 / 操作符时，不仅结果类型的精度在类似查询之间会有所不同，而且结果值也会有所不同。例如，包含表达式 SELECT CAST (1.0 / 7 AS float) 的自动参数化查询的结果值将不同于未自动参数化的同一查询的结果值，因为自动参数化查询的结果将被截断以适合转换为数字 (10, 0) 数据类型。有关参数化查询的更多信息，请参阅简单参数化。
So, you need to convert [val1], 1000 and [val2] to float types:
因此，您需要将 [val1]、1000 和 [val2] 转换为浮点类型：
[Total] AS CAST ( ((CAST ([val1] as float)/CAST (1000 as float)) * CAST ([val2] as float)) AS DECIMAL(18,2)) PERSISTED
回答by Icarus
I came up with this:
我想出了这个：
SELECT (cast(862500 as float)/cast(1000 as float) ) * 8
returns 6900
To make it 2 decimal places....
使其保留 2 个小数位....
SELECT cast((cast(862500 as float)/cast(1000 as float) ) * 8 as decimal(18,2))
returns 6900.00
Your specific case:
您的具体情况：
[Total] AS CAST ( ((cast(val1 as float)/(1000)) * cast([val2] as float)) AS DECIMAL(18,2)) PERSISTED,
