2-1-8 最も近い倍数に、数値を変換

SQLパズル

数値を、最も近い5の倍数に数値を切り上げ、および、
最も近い5の倍数に数値を切り下げる。

テーブル
 Val  切り上げ  切り下げ
----  --------  --------
10.5        15        10
  10        10        10
 9.5        10         5
 5.5        10         5
   5         5         5
 4.5         5         0
 1.5         5         0
   1         5         0
  .5         5         0
   0         0         0
-1.5         0        -5
-4.5         0        -5
  -5        -5        -5
-5.5        -5       -10

SQL

select Val,
ceil(Val/5)  *5 as 切り上げ,
floor(Val/5) *5 as 切り下げ
from (select 10.5 as Val from dual union
      select   10 from dual union
      select  9.5 from dual union
      select  5.5 from dual union
      select    5 from dual union
      select  4.5 from dual union
      select  1.5 from dual union
      select    1 from dual union
      select   .5 from dual union
      select    0 from dual union
      select -1.5 from dual union
      select -4.5 from dual union
      select   -5 from dual union
      select -5.5 from dual)
order by Val desc;

解説

 ceil関数を使えば、数値の切り上げ、
floor関数を使えば、数値の切り下げ、
ができます。

---