首页 > 代码库 > sql server 保留小数(续A)

sql server 保留小数(续A)

写完上一篇文章后,陆续有朋友给出了其他思路,我在小结在这篇博客中。

思路一:转换成字符串后输出(这也是上一篇文章给的思路)

1 1 SELECT  CASE  WHEN @a % @b = 0  THEN CAST(@a / @b AS VARCHAR(16)) 2 2               ELSE  CAST(CAST(@a * 1.0 / @b AS DECIMAL(10,4)) AS VARCHAR(32)) END 12 / 5,3 3         CASE  WHEN @c % @d = 0  THEN CAST(@c / @d AS VARCHAR(16))4 4               ELSE  CAST(CAST(@c * 1.0 / @d AS DECIMAL(10,4)) AS VARCHAR(32)) END 12 / 3
View Code

 

思路二:使用Replace()函数来判断小数点后面的形式

1 DECLARE @a INT,@b INT,@c INT,@d INT2 SET @a    = 123 SET @b  = 54 SET @c = 125 SET @d = 3        6 SELECT REPLACE(CAST(@a * 1.0 / @b AS DECIMAL(10,4)),.0000,‘‘) 12 / 5,7        REPLACE(CAST(@c * 1.0 / @d AS DECIMAL(10,4)),.0000,‘‘) 12 / 3
View Code

 

思路三:使用Round()函数判断四舍五入后的结果与原始结果是否相等

 1 DECLARE @a INT,@b INT,@c INT,@d INT 2 SET @a    = 12 3 SET @b  = 5 4 SET @c = 12 5 SET @d = 3     6 SELECT      CASE   WHEN ROUND(@a * 1.0 / @b,0) = @a * 1.0 / @b  THEN  CAST(@a / @b AS VARCHAR(16)) 7                  ELSE CAST(CAST(@a * 1.0 / @b AS DECIMAL(10,4)) AS VARCHAR(32)) END 12 / 5, 8                   9           CASE   WHEN ROUND(@c * 1.0 / @d,0) = @c * 1.0 / @d THEN  CAST(@c / @d AS VARCHAR(16)) 10                  ELSE CAST(CAST(@c * 1.0 / @d AS DECIMAL(10,4)) AS VARCHAR(32)) END 12 / 3          
View Code

 

以上三种思路的运行结果都是: