NVL NVL2 NULLIF和条件判断函数查询

将空值null转换为实际值
可以使用的数据类型是日期字符和数字
将空值显示成0
select employee_id,first_name,commission_pct,nvl(commission_pct,0)
from employees;

将空值显示成字符
commission_pct是数值型需要转换成字符型
select employee_id,first_name, commission_pct,nvl(to_char(commission_pct),‘kong zhi’)
from employees;

NVL2(expr1,expr2,expr3)
如果exp1为空，则返回exp3，如果exp2不为空，则返回exp2
select employee_id,first_name, commission_pct,
nvl2(commission_pct,commission_pct,0) from employees;
commission_pct=空，返回0 commission_pct=有数值，返回自己本身的值

select employee_id,first_name, commission_pct,
nvl2(commission_pct,‘you zhi’,‘mei you zhi’)
from employees;

nullif： 比较两个表达式，如果相等，则返回null，
select first_name,length(first_name),last_name,length(last_name),
nullif(length(first_name),length(last_name))
from employees;

条件判断
以CASE开头，以END结尾 END 后跟别名
CASE:搜索表达式
select first_name,job_id,salary,
case job_id when ‘IT_PROG’ then 1.20salary
when ‘ST_CLERK’ then 1.15salary
when ‘AD_VP’ then 1.10salary
else salary
end “jieguo”
from employees;

如果没有输入else，则对于不匹配的条件将显示null
select first_name,job_id,salary,
case job_id when ‘IT_PROG’ then 1.20salary
when ‘ST_CLERK’ then 1.15salary
when ‘AD_VP’ then 1.10salary
end “jieguo”
from employees;

如果满足第一个条件，则不管其他条件如何，它都会显示结果
只显示一个结果，这有问题
select salary,
case when salary >3000 then ‘salary > 3000’
when salary >4000 then ‘salary > 4000’
when salary >10000 then ‘salary > 10000’
end cuowu
from employees;

正确的写法 从大到小，显示正确
select salary,
case when salary >10000 then ‘salary > 10000’
when salary >4000 then ‘salary > 4000’
when salary >3000 then ‘salary > 3000’
end zhengque
from employees;

这个好，不用写when then
select last_name,job_id,salary,
decode(job_id,‘IT_PROG’ , 1.20salary,
‘ST_CLERK’ , 1.15salary,
‘AD_VP’ , 1.10salary,
salary )
shuchu
from employees;

如果没有为不匹配条件设置默认值，则这些值将返回null
select last_name,job_id,salary,
decode(job_id,‘IT_PROG’ , 1.20salary,
‘ST_CLERK’ , 1.15salary,
‘AD_VP’ , 1.10salary
)
shuchu
from employees;

判断是否用交税
select last_name,job_id,salary,
case when salary<3000 then ‘0%’
when salary between 3000 and 7000 then ‘10%’
when salary>7000 then ‘20%’
end shui
from employees;