SQL 从 int 转换为十进制

SQL Cast from int to decimal

提问by Darren

I have two columns in a table that are populated with integer values. For each record, I want to divide the values of those two columns and format the output so that the decimal point is moved to places to the right.

我在表中有两列填充了整数值。对于每条记录，我想将这两列的值相除并格式化输出，以便将小数点移到右边的位置。

For example, if I have values of the two columns for one record as 4 and 1 and I want to divide 1 by 4 (so 1/4) then I want the output to be 25.00.

例如，如果我将一条记录的两列的值分别为 4 和 1，并且我想将 1 除以 4（即 1/4），那么我希望输出为 25.00。

Here is the last thing I tried a bit ago:

这是我之前尝试的最后一件事：

CAST(Total AS Decimal(2))/CAST(TotalAnswers AS Decimal(2)) AS 'Percent' 

I have not been able to find a syntactical explanation of the CAST function to see what the parameter passed in for decimal represents. When I change it, it does sometimes change the number of places to the right of the decimal but the results are not always what I expect so I would like to get a little help on this.

我一直没能找到 CAST 函数的句法解释来查看传入的十进制参数代表什么。当我改变它时，它有时会改变小数点右边的位数，但结果并不总是我所期望的，所以我想在这方面得到一些帮助。

回答by Magnus

Write it like this:

像这样写：

SELECT CAST(CAST(Total As float) / TotalAnswers * 100 As decimal(8, 2))

回答by marc_s

DECIMAL(2)is a decimal of 2 digits before and no digits after the decimal point. That doesn't allow you to have digits afterthe decimal point!

DECIMAL(2)是前两位小数，小数点后没有数字。这不允许您在小数点后有数字！

The DECIMAL(p,s)specification defines the number of total digits (p) and the number of digits after the decimal point (s) (where scannot be larger than pand is 0 if ommitted).

的DECIMAL(p,s)规范定义了（总位数的数目p）和小数点后的数字（数目s）（其中，s也不能大于p和是0，如果ommitted）。

You need to use something like DECIMAL(4,2)(4 digit total - 2 of which after the decimal point; so therefore: also 2 before the decimal point) or something like that - some digits before and some after the decimal point - then you'll see your desired result!

您需要使用类似的东西DECIMAL(4,2)（总共 4 位数字 - 其中 2 位在小数点后；因此：小数点前也有 2 位）或类似的东西 -小数点前的一些数字和小数点后的一些数字 - 然后你会看到你的想要的结果！

For details on the DECIMALtype and its syntax, consult the MSDN Books Online topic on decimal and numeric

有关DECIMAL类型及其语法的详细信息，请参阅有关十进制和数字的 MSDN 联机丛书主题

回答by AdaTheDev

There are actual a couple of parameters..

实际上有几个参数..

(p, s)

where

在哪里

• p represents the "precision" - the total number of digits before and afterthe decimal point.
• s represents the "scale" - the number of digits afterthe decimal point.

• p代表“精度”——小数点前后的总位数。
• s 代表“刻度”——小数点后的位数。

By only giving 1 parameter, you are giving the precision only.

通过仅提供 1 个参数，您仅提供了精度。

See MDSN.

请参阅MDSN。

回答by automatic

from book-online for decimal data type: Numeric data types that have fixed precision and scale.

来自在线书籍的十进制数据类型：具有固定精度和小数位数的数字数据类型。

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.

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

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（小数位数）小数点右侧可以存储的最大十进制位数。比例必须是从 0 到 p 的值。只有在指定精度时才能指定比例。默认比例为 0；因此，0 <= s <= p。最大存储大小因精度而异。