SQL将两个整数相除并得到一个十进制值错误 [英] SQL divide two integers and get a decimal value error
问题描述
在SQL语句中,我试图将两个整数相除(下面的代码中整数1为"abc",代码中的整数2为"xyz"),并以小数形式得到结果(代码中的def以下).十进制结果应仅以前导1或0开头,后接一个十进制和十进制后的3个数字.但是我的代码一直返回不带小数的直接0.
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.
SELECT CONVERT(DECIMAL(4,3), abc/xyz) AS def
当我想要的是诸如"0.001"或"0.963"之类的内容时,此代码将导致"0".我相信它仍将"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.
我也尝试过在abc和xyz上使用CAST,但是它返回的是相同的东西.我还尝试了以下代码:
I have also tried using CAST on abc and xyz but it returns the same thing. I have also tried the following code:
SELECT CONVERT(DECIMAL(4,3), abc/xyz) AS CONVERT(DECIMAL(4,3)def)
但这给了我一个错误,说在"CONVERT"一词附近有语法错误.
But this gives me an error, saying there is a syntax error near the word "CONVERT".
推荐答案
在除法之前而不是之后转换为十进制.转换为答案格式.
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
这篇关于SQL将两个整数相除并得到一个十进制值错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!