SQL SQL除以两个整数并得到一个十进制值错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20008201/
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
SQL divide two integers and get a decimal value error
提问by user2762748
In an SQL statement, I am trying to divide two integers (integer 1 is "abc" in my code below, integer 2 is "xyz" in my code), and get a result as a decimal (def in my code below). The decimal result should have a leading 1 or 0 only, followed by a decimal and 3 numbers after the decimal. However my code keeps returning a straight 0 with no decimals.
在 SQL 语句中，我试图将两个整数相除（整数 1 在下面的代码中为“abc”，整数 2 在我的代码中为“xyz”），并以小数形式得到结果（在下面的代码中为 def）。小数结果应仅以 1 或 0 开头，后跟小数点和小数点后的 3 个数字。但是我的代码一直返回一个没有小数的直 0。
SELECT CONVERT(DECIMAL(4,3), abc/xyz) AS def
This code results in "0", when what I want is something like "0.001" or "0.963". I believe that it is still looking at "def" as an integer, and not as a decimal.
此代码导致“0”，当我想要的是“0.001”或“0.963”之类的东西时。我相信它仍然将“def”视为整数，而不是小数。
I have also tried using CAST on abc and xyz but it returns the same thing. I have also tried the following code:
我也试过在 abc 和 xyz 上使用 CAST 但它返回同样的东西。我还尝试了以下代码：
SELECT CONVERT(DECIMAL(4,3), abc/xyz) AS CONVERT(DECIMAL(4,3)def)
But this gives me an error, saying there is a syntax error near the word "CONVERT".
但这给了我一个错误，说“CONVERT”这个词附近有一个语法错误。
回答by asantaballa
Convert to decimal before the divide, not after. The convert for answer format.
在除法之前而不是之后转换为十进制。答案格式的转换。
SELECT
CONVERT( DECIMAL(4,3)
, ( CONVERT(DECIMAL(10,3), abc) / CONVERT(DECIMAL(10,3), xyz) )
) AS def
回答by sam yi
This should do it. Also add divide by zero check.
这应该这样做。还添加除以零检查。
SELECT CONVERT(DECIMAL(4,3), abc) / NULLIF(xyz,0)
回答by David
Try something like
尝试类似的东西
SELECT CONVERT(DECIMAL(4,3), abc/(xyz * 1.0)) AS def
回答by Youbaraj Sharma
Hope this will work...
希望这会奏效...
SELECT CONVERT(DECIMAL(4,3), abc/10.0) AS def
FROM dbo.whatever;
