SQL将两个整数相除得到十进制值错误

2022-01-16 00:00:00 sql asp-classic sql-server

在 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"，而我想要的是0.001"或0.963"之类的东西.我相信它仍然将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

相关文章