MySQL Tutorial/Math Numeric Functions/ROUND
Содержание
How rounding differs for exact and approximate values:
<source lang="sql">
mysql> mysql> SELECT ROUND(2.5), ROUND(25E-1); +------------+--------------+ | ROUND(2.5) | ROUND(25E-1) | +------------+--------------+ | 3 | 2 | +------------+--------------+ 1 row in set (0.00 sec) mysql></source>
ROUND(
<source lang="sql">
mysql> mysql> mysql> SELECT ROUND(1.298, 0); +-----------------+ | ROUND(1.298, 0) | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec) mysql></source>
ROUND(
<source lang="sql">
mysql> mysql> SELECT ROUND(1.298, 1); +-----------------+ | ROUND(1.298, 1) | +-----------------+ | 1.3 | +-----------------+ 1 row in set (0.00 sec) mysql></source>
ROUND(150.000,2)/ROUND(150,2)
<source lang="sql">
mysql> mysql> SELECT ROUND(150.000,2), ROUND(150,2); +------------------+--------------+ | ROUND(150.000,2) | ROUND(150,2) | +------------------+--------------+ | 150.00 | 150.00 | +------------------+--------------+ 1 row in set (0.01 sec) mysql></source>
ROUND(23.298, -1)
<source lang="sql">
mysql> mysql> SELECT ROUND(23.298, -1); +-------------------+ | ROUND(23.298, -1) | +-------------------+ | 20 | +-------------------+ 1 row in set (0.00 sec) mysql> mysql></source>
Rounding value in a column
<source lang="sql">
mysql> mysql> create table myTable(
-> id int(2), -> value FLOAT(6,2) -> );
Query OK, 0 rows affected (0.16 sec) mysql> mysql> insert into myTable(ID, value)values (1,9); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (2,2.11); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (3,3.44); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (4,-4.21); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (5,10); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (6,3); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (7,-5.88); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (8,123.45); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (9,98.23); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (10,938.23); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (11,984.23); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (12,198.23); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (13,928.87); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (14,25.37); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (15,918.3); Query OK, 1 row affected (0.00 sec) mysql> insert into myTable(ID, value)values (16,9.23); Query OK, 1 row affected (0.02 sec) mysql> insert into myTable(ID, value)values (17,8.23); Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from myTable; +------+--------+ | id | value | +------+--------+ | 1 | 9.00 | | 2 | 2.11 | | 3 | 3.44 | | 4 | -4.21 | | 5 | 10.00 | | 6 | 3.00 | | 7 | -5.88 | | 8 | 123.45 | | 9 | 98.23 | | 10 | 938.23 | | 11 | 984.23 | | 12 | 198.23 | | 13 | 928.87 | | 14 | 25.37 | | 15 | 918.30 | | 16 | 9.23 | | 17 | 8.23 | +------+--------+ 17 rows in set (0.00 sec) mysql> mysql> mysql> select value, ROUND(value) from myTable; +--------+--------------+ | value | ROUND(value) | +--------+--------------+ | 9.00 | 9 | | 2.11 | 2 | | 3.44 | 3 | | -4.21 | -4 | | 10.00 | 10 | | 3.00 | 3 | | -5.88 | -6 | | 123.45 | 123 | | 98.23 | 98 | | 938.23 | 938 | | 984.23 | 984 | | 198.23 | 198 | | 928.87 | 929 | | 25.37 | 25 | | 918.30 | 918 | | 9.23 | 9 | | 8.23 | 8 | +--------+--------------+ 17 rows in set (0.00 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.02 sec) mysql> mysql></source>
ROUND(X), ROUND(X,D) rounds the argument X to D decimal places.
- D defaults to 0 if not specified.
- D can be negative to cause D digits left of the decimal point of the value X to become zero.
<source lang="sql">
mysql> mysql> SELECT ROUND(-1.23); +--------------+ | ROUND(-1.23) | +--------------+ | -1 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(-1.58); +--------------+ | ROUND(-1.58) | +--------------+ | -2 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(1.58); +-------------+ | ROUND(1.58) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec)</source>
