SQL Server/T-SQL Tutorial/Math Functions/ROUND
Содержание
- 1 Rounding present value to two decimal places.
- 2 ROUND(salary *
- 3 Rounds a value to the number of decimal places specified in the second parameter.
- 4 select ROUND(12.4999,0)
- 5 select ROUND(12.4999,1)
- 6 select ROUND(12.4999,-1) (Minus)
- 7 select ROUND(12.5,0)
- 8 select ROUND(12.5,0,1)
- 9 SELECT ROUND(3. RoundedNumber
Rounding present value to two decimal places.
<source lang="sql">
4> 5> 6> SELECT ROUND(1464*(1/POWER((1+.10),4)),2) 7> GO
1002.740000000000000000000000000000
(1 rows affected) 1></source>
ROUND(salary *
<source lang="sql">
3> CREATE TABLE employee( 4> id INTEGER NOT NULL PRIMARY KEY, 5> first_name VARCHAR(10), 6> last_name VARCHAR(10), 7> salary DECIMAL(10,2), 8> start_Date DATETIME, 9> region VARCHAR(10), 10> city VARCHAR(20), 11> managerid INTEGER 12> ); 13> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3 2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4 3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5 4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6 5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7 6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8 7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9 8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9 9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> UPDATE Employee 4> SET salary = ROUND(salary * 1.1, 2) 5> GO (9 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 6479.00 2005-03-22 00:00:00.000 North Vancouver 3 2 Alison Mathews 5267.90 2003-07-21 00:00:00.000 South Utown 4 3 James Smith 7345.80 2001-12-01 00:00:00.000 North Paris 5 4 Celia Rice 6123.70 2006-03-03 00:00:00.000 South London 6 5 Robert Black 4913.70 2004-07-02 00:00:00.000 East Newton 7 6 Linda Green 7101.60 2002-05-19 00:00:00.000 East Calgary 8 7 David Larry 5879.50 2008-03-18 00:00:00.000 West New York 9 8 James Cat 4657.40 2007-07-17 00:00:00.000 West Regina 9 9 Joan Act 6735.30 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> drop table employee; 4> GO 1></source>
Rounds a value to the number of decimal places specified in the second parameter.
<source lang="sql">
ROUND(number_to_round, number_ of_places,[0|1]) A numeric of anything but 0 will mean the number is truncated. 9> 10> SELECT ROUND(100.345678,5,0) 11> GO
100.345680
(1 rows affected) 1> SELECT ROUND(100.345678,5,1) 2> GO
100.345670
(1 rows affected) 1> SELECT ROUND(107.345678,-1,0) 2> GO
110.000000
(1 rows affected)</source>
select ROUND(12.4999,0)
<source lang="sql">
3> select ROUND(12.4999,0) 4> GO
12.0000
(1 rows affected) 1> 2></source>
select ROUND(12.4999,1)
<source lang="sql">
4> select ROUND(12.4999,1) 5> GO
12.5000
(1 rows affected) 1> 2></source>
select ROUND(12.4999,-1) (Minus)
<source lang="sql">
4> select ROUND(12.4999,-1) 5> GO
10.0000
(1 rows affected) 1></source>
select ROUND(12.5,0)
<source lang="sql">
5> select ROUND(12.5,0) 6> GO
13.0
(1 rows affected) 1></source>
select ROUND(12.5,0,1)
<source lang="sql">
3> select ROUND(12.5,0,1) 4> GO
12.0
(1 rows affected) 1></source>
SELECT ROUND(3. RoundedNumber
<source lang="sql">
3> 4> SELECT ROUND(3.22245, 3) RoundedNumber 5> GO RoundedNumber
3.22200
(1 rows affected)</source>