极客笔记SQL 常用函数：NVL、NVL2、DECODE、COALESCE、NULLIF、LNNVL和NANVL
简介
今天，我们将学习关于SQL常用函数的知识。我们要学习的常用函数有：
- NVL()
- NVL2()
- DECODE()
- COALESCE()
- LNNVL()
1.) NVL()
这是在结构化查询语言（SQL）中广泛使用的函数之一。
此函数只能接受两个输入值。如果输入值超过2个，则返回错误。该函数在查找时返回第一个非空值。
如果两个输入都为NULL，则此函数没有输出。
输入数据类型可以是整数、浮点数、字符串、字符等。
语法
NVL (input 1, input 2)
示例查询
SQL> select NVL(1, 2) from dual;
NVL(1,2)
_ _ _ _ _ _
1
SQL> select NVL(NULL, 1) from dual;
NVL(NULL,1)
_ _ _ _ _ _ _ _
1
SQL> select NVL(1.029384, 1.029384) from dual;
NVL(1.029384,1.029384)
_ _ _ _ _ _ _ _ _ _ _ _ _ _
1.029384
SQL> select NVL(NULL, 1.029384) from dual;
NVL(NULL,1.029384)
_ _ _ _ _ _ _ _ _ _ _ _
1.029384
SQL> select NVL('JOE', 'ROOT') from dual;
NVL
_ _ _
JOE
SQL> select NVL(NULL, 'ROOT') from dual;
NVL(
_ _ _ _
ROOT
SQL> select COMMISION_PERCENTAGE from sal;
COMMISION_PERCENTAGE
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.15
.1
.01
.33
SQL> select NVL(COMMISION_PERCENTAGE, 0) from sal;
NVL(COMMISION_PERCENTAGE,0)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.15
.1
.01
0
0
.33
0
0
8 rows selected.
SQL> SELECT id, name, sal, NVL (COMMISION_PERCENTAGE, 0),
2 (sal) + (sal * NVL (COMMISION_PERCENTAGE, 0))
3 monthly_salary FROM sal;
ID NAME SAL NVL(COMMISION_PERCENTAGE,0) MONTHLY_SALARY
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Root 75000 0.15 86250
2 Ros Taylor 90000 0.1 99000
3 Paul Adams 50000 0.01 50500
4 Victor Lee 43000 0 43000
5 Matt Potts 20000 0 20000
6 James Anderson 200000 0.33 266000
7 Craig Overton 11000 0 11000
8 Rory Burns 9000 0 9000
8 rows selected.
SQL> SELECT id, name, sal, NVL (COMMISION_PERCENTAGE, 0),
2 (sal * 12) + (sal * NVL (COMMISION_PERCENTAGE, 0) *12)
3 yearly_salary FROM sal;
ID NAME SAL NVL(COMMISION_PERCENTAGE,0) YEARLY_SALARY
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Root 75000 0.15 1035000
2 Ros Taylor 90000 0.1 1188000
3 Paul Adams 50000 0.01 606000
4 Victor Lee 43000 0 516000
5 Matt Potts 20000 0 240000
6 James Anderson 200000 0.33 3192000
7 Craig Overton 11000 0 132000
8 Rory Burns 9000 0 108000
8 rows selected.
2.) NVL2()
这是SQL中广泛使用的一个函数（Structured Query Language）。
这个函数只能接受三个输入值。如果输入值超过三个，则返回错误。当在函数中搜索时，该函数返回第一个非NULL值之后的值。
如果第一个值不是NULL，则返回第二个值。
如果第一个值是NULL且第二个值不是NULL，则返回第三个值。
返回值也可以是NULL值。
其工作原理与SQL中的NVL()相同。
如果两个输入都是NULL，则该函数没有输出。
输入数据类型可以是整数、浮点数、字符串、字符输入等。
语法
NVL2(input 1, input 2, input 3)
示例查询
SQL> select NVL2(1, 2, 3) from dual;
NVL2(1,2,3)
_ _ _ _ _ _ _ _
2
SQL> select NVL2(2, 2, 3) from dual;
NVL2(2,2,3)
_ _ _ _ _ _ _ _
2
SQL> select NVL2(2, 4, 3) from dual;
NVL2(2,4,3)
_ _ _ _ _ _ _ _
4
SQL> select NVL2(2, NULL, 3) from dual;
NVL2(2,NULL,3)
_ _ _ _ _ _ _ _ _
SQL> select NVL2('Kevin', 'Pitersen', ' SA / ENG ') from dual;
NVL2('KE
_ _ _ _ _ _
Pitersen
SQL> select NVL2('NULL', 'Pitersen', ' SA / ENG ') from dual;
NVL2('NU
_ _ _ _ _ _
Pitersen
SQL> select NVL2(NULL, 'Pitersen', ' SA / ENG ') from dual;
NVL2(NULL,
_ _ _ _ _ _
SA / ENG
SQL> select NVL2('Kevin', NULL, ' SA / ENG ') from dual;
N
_
SQL> select NVL2(56.2, 35.6, 23.4) from dual;
NVL2(56.2,35.6,23.4)
_ _ _ _ _ _ _ _ _ _ _ _
35.6
SQL> select NVL2(NULL, 35.6, 23.4) from dual;
NVL2(NULL,35.6,23.4)
_ _ _ _ _ _ _ _ _ _ _ _ _
23.4
SQL> select NVL2(NULL, NULL, 23.4) from dual;
NVL2(NULL,NULL,23.4)
_ _ _ _ _ _ _ _ _ _ _ _ _
23.4
SQL> select NVL2(NULL, NULL, NULL) from dual;
N
_
SQL> select NVL2(NULL, 23.4, NULL) from dual;
NVL2(NULL,23.4,NULL)
_ _ _ _ _ _ _ _ _ _ _ _ _ _
SQL> select NVL2(56.2, 23.4, NULL) from dual;
NVL2(56.2,23.4,NULL)
_ _ _ _ _ _ _ _ _ _ _ _ _
23.4
SQL > select * from sal;
ID NAME SAL COMMISION_PERCENTAGE
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Root 75000 0.15
2 Ros Taylor 90000 0.1
3 Paul Adams 50000 0.01
4 Victor Lee 43000
5 Matt Potts 20000
6 James Anderson 200000 0.33
7 Craig Overton 11000
8 Rory Burns 9000
8 rows selected.
SQL> SELECT id, name, sal, NVL2(COMMISION_PERCENTAGE, sal, 0),
2 (sal) + (sal * NVL2(COMMISION_PERCENTAGE, NULL, 0.1))
3 Wierd_salary FROM sal;
ID NAME SAL NVL2(COMMISION_PERCENTAGE,sal,0) WEIRD_SALARY
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Root 75000 75000
2 Ros Taylor 90000 90000
3 Paul Adams 50000 50000
4 Victor Lee 43000 0 47300
5 Matt Potts 20000 0 22000
6 James Anderson 200000 200000
7 Craig Overton 11000 0 12100
8 Rory Burns 9000 0 9900
3.) DECODE()
这也是SQL中使用的表达式之一。这个解码表达式用作IF、ELSE IF、ELSE IF Ladder风格。此解码是基于指定条件工作的。
任何指定的操作在这里都可以工作。
输入类型必须根据指定的数据类型选择。
语法
DECODE (column name, number 1 to be searched, result 1 to be updated
, number 2 to be searched, result 2 to be updated
, number 3 to be searched, result 3 to be updated
. . . . . . . . .
number n to be searched, result n to be updated , default)
示例查询
SQL > select * from ipla;
SID SNAME SAL AGE
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 mahi 12 40
2 kohli 14 33
3 DK 6.25 33
4 warner 6.75 33
5 rahul 16 29
6 pandya 14 27
SQL > SELECT Sname, sid, sal,
2 DECODE (sid, 1, 1.5*sal,
3 2, 4*sal,
4 3, 9*sal,
5 4, 10.25*sal,
6 sal)
7 "REVISED SALARY"
8 from ipla ;
SNAME SID SAL REVISED SALARY
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
mahi 1 12 18
kohli 2 14 56
DK 3 6.25 56.25
warner 4 6.75 69.1875
rahul 5 16 16
pandya 6 14 14
6 rows selected.
SQL > select * from ipla;
SID SNAME SAL AGE
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 mahi 12 40
2 kohli 14 33
3 DK 6.25 33
4 warner 6.75 33
5 rahul 16 29
6 pandya 14 27
7 Tim David 8.25 26
SQL > SELECT Sid, Sname, Sal, Age,
2 DECODE (sid, 1, 2 * sid * sal,
3 2, 3 * sid * sal,
4 3, 5 * sid * sal,
5 4, 10 * sid * sal,
6 5, 12 * sid * sal,
7 6, 15 * sid * sal,
8 sal/2)
9 "UPGRADED SALARY"
10 from ipla ;
SID SNAME SAL AGE UPGRADED SALARY
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 mahi 12 40 24
2 kohli 14 33 84
3 DK 6.25 33 93.75
4 warner 6.75 33 270
5 rahul 16 29 960
6 pandya 14 27 1260
7 Tim David 8.25 26 4.125
7 rows selected.
4.) COALESCE()
这也是SQL中使用的表达式之一。该表达式与NVL()表达式的工作方式类似。唯一的区别是它可以接受两个以上的输入。它返回第一个非空的输入元素。
输入数据类型可以是任何类型。输入可以是int、float、string、character、number等。
语法
COALESCE (input 1, input 2, input 3, . . . . . . . . . ., input n)
示例查询
SQL> select COALESCE(NULL, 1) from dual;
COALESCE(NULL,1)
_ _ _ _ _ _ _ _ _ _ _
1
SQL> select COALESCE(1, 2, 2) from dual;
COALESCE(1,2,2)
_ _ _ _ _ _ _ _ _ _ _
1
SQL> select COALESCE(NULL, 2, 2) from dual;
COALESCE(NULL,2,2)
_ _ _ _ _ _ _ _ _ _ _ _
2
SQL> select COALESCE(NULL, NULL, 2) from dual;
COALESCE(NULL,NULL,2)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _
2
SQL> select COALESCE(NULL, NULL, NULL) from dual;
C
_
SQL> select COALESCE(NULL, NULL, NULL, 1, 2, 3, 4, 5, 6) from dual;
COALESCE(NULL,NULL,NULL,1,2,3,4,5,6)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1
SQL> select COALESCE(NULL, 'NULL', 'Stuart Broad', 'Adam Gilchrist') from dual;
COAL
_ _ _ _
NULL
5.) LNNVL()
这是SQL中的一个函数，用于将True转换为False，将False转换为True。
LNNVL()函数具有保存条件的能力。这使得条件反转。
如果条件是SID = 2。那么LNNVL(SID = 2)等价于SID != 2。
语法
LNNVL (Condition)
示例查询
SQL> select * from ipla;
SID SNAME SAL AGE
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 mahi 12 40
2 kohli 14 33
3 DK 6.25 33
4 warner 6.75 33
5 rahul 16 29
6 pandya 14 27
7 Tim David 8.25 26
7 rows selected.
SQL> select * from ipla where sid=2;
SID SNAME SAL AGE
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
2 kohli 14 33
SQL> select * from ipla where LNNVL (sid = 2) ;
SID SNAME SAL AGE
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 mahi 12 40
3 DK 6.25 33
4 warner 6.75 33
5 rahul 16 29
6 pandya 14 27
7 Tim David 8.25 26
6 rows selected.
SQL> select * from sal;
ID NAME SAL COMMISION_PERCENTAGE
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Root 75000 0.15
2 Ros Taylor 90000 0.1
3 Paul Adams 50000 0.01
4 Victor Lee 43000
5 Matt Potts 20000
6 James Anderson 200000 0.33
7 Craig Overton 11000
8 Rory Burns 9000
8 rows selected
SQL> select * from sal where NVL (COMMISION_PERCENTAGE, 0) =0;
ID NAME SAL COMMISION_PERCENTAGE
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
4 Victor Lee 43000
5 Matt Potts 20000
7 Craig Overton 11000
8 Rory Burns 9000
4 rows selected.
SQL> select * from sal where LNNVL (NVL (COMMISION_PERCENTAGE, 0) = 0) ;
ID NAME SAL COMMISION_PERCENTAGE
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 Joe Root 75000 0.15
2 Ros Taylor 90000 0.1
3 Paul Adams 50000 0.01
6 James Anderson 200000 0.33
4 rows selected
6.) NANVL ()
如果输入值n2是NaN（非数字），则此方法返回替代值n1，如果n2不是NaN，则返回n2。只有类型为BINARY FLOAT或BINARY DOUBLE的浮点数可以与此函数一起使用。
该函数接受任何数字或非数字数据类型作为输入，并具有隐式转换为数字数据类型的能力。
如果参数是BINARY FLOAT，则该方法返回BINARY DOUBLE。否则，该函数返回与参数匹配的数字数据类型。
语法
NANVL (input 1, input 2)
示例查询
SQL> SELECT * FROM FPT;
DEC_NUM BIN_DOUBLE BIN_FLOAT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
3563.971 3.564E+003 3.564E+003
SQL> INSERT INTO FPT VALUES (0, 'NaN', 'NaN');
1 row created.
SQL> SELECT * FROM FPT ;
DEC_NUM BIN_DOUBLE BIN_FLOAT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
3563.971 3.564E+003 3.564E+003
0 Nan Nan
SQL> SELECT bin_float, NANVL (bin_float,0)
2 FROM float_point_test;
BIN_FLOAT NANVL(BIN_FLOAT,0)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1.514E+003 1.514E+003
Nan 0
MULTI QUERY APPROACH
SQL> select * from ipla;
SID SNAME SAL AGE
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 mahi 12 40
2 kohli 14 33
3 DK 6.25 33
4 warner 6.75 33
5 rahul 16 29
6 pandya 14 27
7 Tim David 8.25 26
SQL> select Sname, sid, sal,
2 DECODE (SID, 1, (sal + sid * 5),
3 2, (sal + sid*4),
4 3, NVL(sal*4, NULL),
5 4, NVL2(NULL, sal*3, sal*4),
6 5, COALESCE(NULL, NULL, sal+4),
7 6, 3 * sal,
8 sal*2)
9 "REVISED SALARY "
10 from ipla;
SID SNAME SAL REVISED SALARY
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
1 mahi 12 17
2 kohli 14 22
3 DK 6.25 25
4 warner 6.75 27
5 rahul 16 20
6 pandya 14 42
7 Tim David 8.25 16.5
这里是关于SQL的常用函数：NVL，NVL2，DECODE，COALESCE，NULLIF，LNNVL和NANVL