在SQL Server中将*向上*取整到最接近的100

select floor((X + 99) / 100) * 100;

结果如下:

0 -> 0
1 -> 100
99 -> 100
100 -> 100
101 -> 200

select round(YourValue, -3)

干杯!!!!!

CEILING() 功能如下:

SELECT CEILING(@value/100.0) * 100

您可能需要先将值转换为十进制数,具体取决于其类型。

使用天花板函数将图形向上舍入

DECLARE @Number DECIMAL, @RoundUp DECIMAL
SET @RoundUp = 100
SET @Number = 720
SELECT CEILING(@Number/@RoundUp)*@RoundUp

通过简单地使用 ROUND 例如:

SELECT ROUND(number/1000,2)*1000 

这将为您提供最接近的千分之一的值。

试试这个:

DECLARE @Foo int
SET @Foo = 720

print @Foo
print (@Foo + 100) % 100
PRINT @Foo - (@Foo + 100) % 100

您可以使用此代码,假设 amount

If amount % 100 != 0 Then
   roundedAmount = ((amount / 100) * 100) + 100
Else
   roundedAmount = amount

user defined function .

select (720 - MOD(720,100)) + 100 from dual;

如果你需要下一个第80名,只要用“80”替换任何“100”。

Gray's 回答, 我将使用以下内联函数:

CREATE FUNCTION dbo.udf_RoundNearest
(
@Number bigint,
@RoundNearest bigint,
@Direction int
)

RETURNS TABLE AS 
RETURN

SELECT CASE WHEN @RoundNearest>=@Number THEN @Number
       ELSE
        (
                (@Number + CASE 
                           WHEN @Direction = 0 --Round Down
                           THEN 0
                           ELSE CASE WHEN @Number % @RoundNearest = 0 THEN 0 ELSE @RoundNearest END
                           END) / @RoundNearest) * @RoundNearest

   END Number

参数定义:

1. @数字-需要舍入的数字
2. @第10、100、1000等
3. @方向 0->圆 1->汇总

使用函数:

SELECT * FROM dbo.udf_RoundNearest (1965,100,1) --> 2000
SELECT * FROM dbo.udf_RoundNearest (1359,100,0) --> 1300
SELECT * FROM dbo.udf_RoundNearest (1999,10,0) --1990
SELECT * FROM dbo.udf_RoundNearest (80,100,0) --> 80 (if the @number parameter is less or equal the @RoundNearest parameter the result will be the @number itself

它还可以用作应用它与表的比较 例如:

;with tmp (Value) as
  (select 1236 union all select 6584 union all select 9999)

    select t.*, fn.Number
    from tmp t
    cross apply dbo.udf_RoundNearest (Value,100,0) fn

    /*Result Set
    Value   Number
    1236    1200
    6584    6500
    9999    9900*/

select floor((ceiling (@value) + 99) / 100) * 100;

这对我很管用。

Round(@value/100, 0) * 100

@roundUpValue = ((@intValue / 1000) + 1) * 1000
@roundDownValue = (@intValue / 1000) * 1000

例如

declare @intValue as int = 1934
select ((@intValue / 1000) + 1) * 1000 as roundUp
select (@intValue / 1000) * 1000 as roundDown

select ((@intValue / 500) + 1) * 500 as roundUp