SQL中的自定义舍入

Question

如何为SQL中的自定义舍入设置规则？

我需要制定这个规则：

if second number after decimal is 1-2   eg.  10,02 - round it to 0  result 10,00
if second number after decimal is 3-7   eg.  10,13 - round it to 5  result 10,15
if second number after decimal is 8-9   eg.  10,28 - round it to bigger number  result 10,3

有人能帮我怎么做吗？圆函数工作在0-5及以上5，但如何根据这一规则进行“自定义舍入函数”？

提前谢谢。

Answer 1

一种可能的办法是以下声明：

SELECT
   [Number],
   CASE 
      WHEN ([Number] * 100) % 10 BETWEEN 3 AND 7 THEN ROUND([Number] + 0.02, 1) - 0.05
      ELSE ROUND([Number] + 0.02, 1)
   END [RoundedNumber]  
FROM (VALUES (10.00), (10.01), (10.12), (10.13), (10.14), (10.28), (10.29)) v ([Number])

结果：

Number  RoundedNumber
10.00   10.00
10.01   10.00
10.12   10.10
10.13   10.15
10.14   10.15
10.28   10.30
10.29   10.30

Answer 2

您可以创建自己的函数来使用自定义舍入规则：

CREATE FUNCTION CustomRound(@num DECIMAL(18,2))
RETURNS DECIMAL(18,2)
AS
BEGIN
  RETURN ROUND(@num, 1, 1) + 
    CASE WHEN (@num - ROUND(@num, 1, 1)) * 100 BETWEEN 1 AND 2 THEN 0
         WHEN (@num - ROUND(@num, 1, 1)) * 100 BETWEEN 3 AND 7 THEN 0.05
         WHEN (@num - ROUND(@num, 1, 1)) * 100 BETWEEN 8 AND 9 THEN 0.1
    END
END

该函数在第一个小数位之后截断小数，并根据第二个小数位的不同，根据需要将一个值加到“整数”。

您可以像这样使用上面的函数：

SELECT dbo.CustomRound(10.12) -- 10.10
SELECT dbo.CustomRound(10.02) -- 10.00
SELECT dbo.CustomRound(10.13) -- 10.15
SELECT dbo.CustomRound(10.28) -- 10.30

您还可以使用自定义四舍五入扩展此函数以使其更具动态性：

CREATE FUNCTION CustomRound(@num DECIMAL(18,6), @precision INT)
RETURNS DECIMAL(18,6)
AS
BEGIN
  DECLARE @prec INT = IIF(@precision > 0, @precision - 1, 0);
  
  RETURN ROUND(@num, @prec, 1) + 
    CASE WHEN (@num - ROUND(@num, @prec, 1)) * POWER(10, @precision) BETWEEN 1 AND 2 THEN 0.0 / POWER(10, @precision)
         WHEN (@num - ROUND(@num, @prec, 1)) * POWER(10, @precision) BETWEEN 3 AND 7 THEN 5.0 / POWER(10, @precision)
         WHEN (@num - ROUND(@num, @prec, 1)) * POWER(10, @precision) BETWEEN 8 AND 9 THEN 10.0 / POWER(10, @precision)
    END
END

您可以像这样使用这个函数：

SELECT dbo.CustomRound(10.12, 2) -- 10.10
SELECT dbo.CustomRound(10.02, 2) -- 10.00
SELECT dbo.CustomRound(10.13, 2) -- 10.15
SELECT dbo.CustomRound(10.28, 2) -- 10.30

-- or    
SELECT dbo.CustomRound(10.102, 3) -- 10.100
SELECT dbo.CustomRound(10.002, 3) -- 10.000
SELECT dbo.CustomRound(10.103, 3) -- 10.105
SELECT dbo.CustomRound(10.208, 3) -- 10.210

Answer 3

您可以定义您的custome功能并永远使用它。

DECLARE @NUM REAL =10.01;
DECLARE @RESULT NVARCHAR(MAX)

IF @NUM=ROUND(@NUM,0)
BEGIN
    SET @RESULT=@NUM
END
ELSE
BEGIN
    DECLARE @NUM_STR VARCHAR(MAX)=CAST(@NUM AS VARCHAR(MAX)) -- CONVERT TO VARCHAR
    DECLARE @LEFT VARCHAR(MAX)= CASE WHEN @NUM>=0 THEN FLOOR(@NUM) ELSE CEILING(@NUM) END -- GET LEFT SIDE NUMBERS
    DECLARE @RIGHT VARCHAR(MAX)= REPLACE(@NUM_STR,CONCAT(@LEFT,'.'),'') -- GET RIGHT SIDE NUMBERS
    ... -- YOUR ROLES
END