SQL Server/T-SQL Tutorial/Math Functions/FLOOR
Содержание
CASE with FLOOR function
4> CREATE TABLE Products (
5> ProductID int NOT NULL ,
6> ProductName nvarchar (40) NOT NULL ,
7> SupplierID int NULL ,
8> CategoryID int NULL ,
9> QuantityPerUnit nvarchar (20) NULL ,
10> UnitPrice money NULL,
11> UnitsInStock smallint NULL,
12> UnitsOnOrder smallint NULL,
13> ReorderLevel smallint NULL,
14> Discontinued bit NOT NULL
15> )
16> GO
1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6)
2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7)
3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0)
4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0)
5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0)
6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0)
7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0)
8> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> DECLARE @Markup money
3> DECLARE @Multiplier money
4>
5> SELECT @Markup = .10
6> SELECT @Multiplier = @Markup + 1
7>
8> SELECT TOP 10 ProductID, ProductName, UnitPrice,
9> UnitPrice * @Multiplier AS "Marked Up Price", "New Price" =
10> CASE WHEN FLOOR(UnitPrice * @Multiplier + .24)
11> > FLOOR(UnitPrice * @Multiplier)
12> THEN FLOOR(UnitPrice * @Multiplier) + .95
13> WHEN FLOOR(UnitPrice * @Multiplier + .5) >
14> FLOOR(UnitPrice * @Multiplier)
15> THEN FLOOR(UnitPrice * @Multiplier) + .75
16> ELSE FLOOR(UnitPrice * @Multiplier) + .49
17> END
18> FROM Products
19> ORDER BY ProductID DESC
20> GO
ProductID ProductName UnitPrice Marked Up Price New Price
----------- ---------------------------------------- --------------------- --------------------- ----------------------
7 O 13.0000 14.3000 14.4900
6 L 18.0000 19.8000 19.9500
5 R 1.2300 1.3530 1.4900
4 L 10.0000 11.0000 11.4900
3 R 17.0000 18.7000 18.7500
2 M 34.8000 38.2800 38.4900
1 F 61.5000 67.6500 67.7500
(7 rows affected)
1>
2> drop table Products;
3> GO
Computing the modulus with FLOOR.
4>
5>
6> SELECT 5-2*FLOOR(5/2)
7> GO
-----------
1
(1 rows affected)
1>
FLOOR returns the value that represents the largest integer that is less-than or equal to the input_number.
The syntax for the FLOOR function is as follows: FLOOR(input_number)
9> SELECT 5-2*FLOOR(5/2)
10> GO
-----------
1
(1 rows affected)
1>
MIN(FLOOR(price)),
3> CREATE TABLE titles(
4> title_id varchar(20),
5> title varchar(80) NOT NULL,
6> type char(12) NOT NULL,
7> pub_id char(4) NULL,
8> price money NULL,
9> advance money NULL,
10> royalty int NULL,
11> ytd_sales int NULL,
12> notes varchar(200) NULL,
13> pubdate datetime NOT NULL
14> )
15> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT "Low End"=MIN(FLOOR(price)),
4> "High End"=MAX(CEILING(price))
5> FROM titles
6> GO
Low End High End
--------------------- ---------------------
7.0000 20.0000
(1 rows affected)
1>
2> drop table titles;
3> GO
Returning the decimal portion using FLOOR.
4>
5>
6> SELECT 1.22-FLOOR(1.22)
7> GO
--------
.22
(1 rows affected)
1>
select FLOOR(1.25)
2> select FLOOR(1.25)
3> GO
-----
1
(1 rows affected)
1>
select FLOOR(-1.25) (minus)
3> select FLOOR(-1.25)
4> GO
-----
-2
(1 rows affected)