总是在SQL Server中四舍五入 [英] always rounding up in SQL Server

问题描述

你好，

我在带有舍入功能的sql server 2005中有问题.例如，我已将变量声明为十进制并为其设置了一个值:

hello,

i have a problem in sql server 2005 with rounding functions. for example i have declared variable as decimal and set a value to it:

DECLARE @v DECIMAL(8,3)
SET @v = 4.843

现在我必须将此值四舍五入到4.85.有可能这样做吗?标准舍入函数(舍入，上限)无法做到.

now i have to round this value up to value 4.85. is there any possibility to do that? standard rounding functions (rounding, ceiling) can''t do that.

推荐答案

测试它:

DECLARE @v DECIMAL(8,3)
DECLARE @x DECIMAL(8,3)

SET @v = 4.839
SET @x = 0.001

CREATE TABLE #tt ([V] DECIMAL(8,3), [AkaModuloV] DECIMAL(8,3),  [RoundedV] DECIMAL(8,3))


WHILE (@v<4.85)
BEGIN
	SET @v = @v + @x
	INSERT INTO #tt([V], [AkaModuloV], [RoundedV])
	SELECT @v AS [V], (@v - ROUND(@v, 2)) AS [AkaModuloV], [RoundedV]  = CASE 
			WHEN ((@v - ROUND(@v, 2))=0.000) THEN @v
			WHEN ((@v - ROUND(@v, 2))<0.000) THEN ROUND(@v, 2)
			WHEN ((@v - ROUND(@v, 2))>0.000) THEN ROUND(@v + (0.009 - (@v - ROUND(@v, 2))), 2)
		END
END

SELECT *
FROM #tt

DROP TABLE #tt

结果:

Results:

V    AkaModuloV  RoundedV
4.840	0.000	4.840
4.841	0.001	4.850
4.842	0.002	4.850
4.843	0.003	4.850
4.844	0.004	4.850
4.845	-0.005	4.850
4.846	-0.004	4.850
4.847	-0.003	4.850
4.848	-0.002	4.850
4.849	-0.001	4.850
4.850	0.000	4.850

请参见以下示例

see below example

select convert(numeric(18,2),2.237)

您可以使用功能转换

you can use function convert

convert(numeric(18,2),Input value)
or
convert(Decimal(18,2),Input value)

祝您编码愉快！
:)

Happy Coding!
:)

尝试一下

try this

DECLARE @v DECIMAL(8,3)
SET @v = 4.846
SELECT Cast(ROUND(@v, 2) as decimal(8,2))

这将给我4.85

this will give me 4.85

DECLARE @v DECIMAL(8,3)
SET @v = 4.843
SELECT Cast(ROUND(@v, 2) as decimal(8,2))

这将给我4.84

this will give me 4.84

这篇关于总是在SQL Server中四舍五入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！