在 T-SQL 中使用条件向上舍入十进制数

Question

我有一个ASP函数，如下所示，用于四舍五入金额：

function GetRoundedVal(amount)
NoOfRight = right(formatnumber(amount,2),1)
if NoOfRight = 0 then
    roundedAmount = amount
elseif NoOfRight = 1 then
    roundedAmount = amount - 0.01
elseif NoOfRight = 2 then
    roundedAmount = amount - 0.02
elseif NoOfRight = 3 then
    roundedAmount = amount + 0.02
elseif NoOfRight = 4 then
    roundedAmount = amount + 0.01
elseif NoOfRight = 5 then
    roundedAmount = amount
elseif NoOfRight = 6 then
    roundedAmount = amount - 0.01
elseif NoOfRight = 7 then
    roundedAmount = amount - 0.02
elseif NoOfRight = 8 then
    roundedAmount = amount + 0.02
elseif NoOfRight = 9 then
    roundedAmount = amount + 0.01
else
end if
GetRoundedVal = roundedAmount
end function

结果应该是这样的：

+----------------+--------+
| Original Value | Result |
+----------------+--------+
| 19.91          | 19.90  | Original Value - 0.01
| 19.92          | 19.90  | Original Value - 0.02
| 19.93          | 19.95  | Original Value + 0.02
| 19.94          | 19.95  | Original Value + 0.01
| 19.95          | 19.95  | 
| 19.96          | 19.95  | Original Value - 0.01
| 19.97          | 19.95  | Original Value - 0.02
| 19.98          | 20.00  | Original Value + 0.02
| 19.99          | 20.00  | Original Value + 0.01
+----------------+--------+

问题是我们可以直接在 T-SQL 上执行此操作吗？
SQL Server V14。
如果可能的话。

谢谢。

Answer 1

你可以

试试这个。

DECLARE @MyTable TABLE(OriginalValue DECIMAL(18,2))
INSERT INTO @MyTable VALUES (19.91), (19.92), (19.93), (19.94), (19.95),    (19.96), (19.97), (19.98), (19.99)
SELECT 
       OriginalValue, 
       ROUND( ( OriginalValue / 0.05 ), 0, 0 ) * 0.05 Result 
FROM @MyTable

结果：

OriginalValue                           Result
--------------------------------------- ---------------------------------------
19.91                                   19.90000000
19.92                                   19.90000000
19.93                                   19.95000000
19.94                                   19.95000000
19.95                                   19.95000000
19.96                                   19.95000000
19.97                                   19.95000000
19.98                                   20.00000000
19.99                                   20.00000000

Answer 2

试试这个

DECLARE @Inp TABLE
(
    Org DECIMAL(10,4),
    Res DECIMAL(10,2)
)
INSERT INTO @Inp
(
    Org
)
VALUES(19.91),
    (19.92),
    (19.93),
    (19.94),
    (19.95),
    (19.96),
    (19.97),
    (19.98),
    (19.99)
SELECT
    *,
    Result = CAST(
    Org +
    CASE RIGHT(CAST(Org AS DECIMAL(10,2)),1)
       WHEN 1 THEN -0.01
       WHEN 2 THEN -0.02
       WHEN 3 THEN 0.02
       WHEN 4 THEN 0.01
       WHEN 5 THEN 0
       WHEN 6 THEN -0.01
       WHEN 7 THEN -0.02
       WHEN 8 THEN 0.02
       WHEN 9 THEN 0.01
       ELSE 0 END
       AS DECIMAL(10,2))
    FROM @Inp

----------