Oracle中的单列函数-非聚合函数

目录

一、nvl(参1,参2)

二、greatest(参1,参2...)

三、least(参1,参2...)

四、coalesce(参1,参2...)

五、nvl2(参1,参2,参3)

六、case when

6.1（一）第一种情况

6.2(二)第二种情况

七、decode(列,列中值1,值1,列中值2,值2...[值n])

八、nullif(参数1,参数2)

---

一、nvl(参1,参2)

参数1为空返回参数2，否则返回参数1；两个参数数据类型要一致。

NVL函数用于将NULL值替换为指定的值。如果第一个参数为NULL，NVL函数将返回第二个参数，否则返回第一个参数。语法如下：

NVL(expr1, expr2)

其中，expr1和expr2可以是任意非LOB类型的表达式。如果expr1为NULL，则返回expr2，否则返回expr1。

适用场景：

NVL函数适用于需要将NULL值替换为指定值的场景，如：

• - 在查询结果中将NULL值替换为默认值；
• - 在计算中将NULL值替换为0或其他默认值。

例如，查询employees表中每个员工的薪水和奖金，并将NULL值替换为0：

SELECT first_name, last_name, NVL(salary, 0), NVL(commission_pct, 0)
FROM employees;

这条语句将查询employees表中每个员工的薪水和奖金，并使用NVL函数将NULL值替换为0。如果某个员工的薪水或奖金为NULL，查询结果中将显示0。

查询姓名,经理编号（如果经理编号为空返回666）：

select ename,nvl(mgr,666)
from emp;

查询每个人的姓名和他的月薪（月薪=工资+提成）：

select ename,sal+nvl(comm,0)
from emp;

二、greatest(参1,参2...)

求多个参数中的最大值，参数的数据类型要一致，其中空最大

select greatest(1,2,'3',4,5,''),greatest('a','b','c')

from dual;

三、least(参1,参2...)

求多个参数中的最小值，参数的数据类型要一致，其中空最小

select least(1,2,'3',4,5,''),least('a','b','c')
from dual;

四、coalesce(参1,参2...)

求多个参数中第一个不为空的值，参数的数据类型要一致，不接受隐式转换。

select coalesce(null,1,2,3)
from dual;

比如：查询提成,经理编号,工资以及它们中第一个不为空的值：

select comm,mgr,sal,coalesce(comm,mgr,sal)
from emp;

五、nvl2(参1,参2,参3)

参1为空返回参3否则返回参2；比如：

如果经理编号为空返回 '老大' 否则返回 '小弟'：

select nvl2(mgr,'小弟','老大')
from emp;

如果comm列为空或者为0 返回 '无奖金' 否则返回 '有奖金'

select nvl2(replace(comm,0),'有奖金','无奖金')
from emp;

六、case when

6.1（一）第一种情况

语法：

select case 列 when 列中值1 then 值1

when 列中值2 then 值2

...

[else 值n]

end

from 表

比如：

如果部门编号是10部门返回 十部门；20部门 返回 二十部门；30部门 返回 三十部门

select case deptno when 10 then '十部门'
                   when 20 then '二十部门'
                   else '三十部门'
        end 部门,deptno
from  emp;

查询员工表的职位以及中文职位

select job,case job when 'CLERK' then '职员'
                    when 'SALESMAN' then '销售'
                    when 'MANAGER' then '经理'
                    when 'PRESIDENT' then '董事长'
                    else '分析' 
        end 职位
from  emp;

6.2(二)第二种情况

select case when 条件1 then 值1

when 条件2 then 值2

...

[else 值n]

end

from 表

比如：

如果部门编号是10部门返回 十部门；20部门 返回 二十部门；30部门 返回 三十部门

select case  when deptno=10 then '十部门'
             when deptno=20 then '二十部门'
             else '三十部门'
        end 部门
from  emp;

如果工资大于等于3000 返回 '一级'；大于等于2000 返回 '二级'；大于等于1000 返回 '三级'；否则返回 '四级'：

select case  when sal>=3000 then '一级'
             when sal>=2000 then '二级'
             when sal>=1000 then '三级'
             else '四级'
        end 工资等级
from  emp;

如果工资小于等于8000 返回 '1级'；小于等于4000 返回 '2级'；小于等于2000 返回 '3级'；小于等于1000 返回 '4级'；查询每个人的姓名 工资 工资等级：

select case  when sal<=1000 then '4级'
             when sal<=2000 then '3级'
             when sal<=4000 then '2级'
             else '1级'
        end 工资等级,ename,sal
from  emp ; --需要倒着写

--补全范围
select case  when sal<=8000 and sal>4000 then '1级'
             when sal<=4000 and sal>2000 then '2级'
             when sal<=2000 and sal>1000 then '3级'
             else '4级'
        end 工资等级,ename,sal
from  emp ;

如果是1981年之前入职 ，返回 他的部门编号；如果是1981-1982年入职 ，返回 他入职的年份；如果是1982年之后入职 ，返回 他入职的月份：

select case when hiredate<date'1981-1-1' then to_char(deptno)
            when hiredate between date'1981-1-1' 
              and date'1982-12-31' then to_char(hiredate,'yyyy')
            when hiredate>date'1982-12-31' then to_char(hiredate,'mm')
       end albaba
from emp;

七、decode(列,列中值1,值1,列中值2,值2...[值n])

相当于case when 的第一种 情况；比如：

如果部门编号是10部门返回 十部门；20部门 返回 二十部门；30部门 返回 三十部门：

select decode(deptno,10,'十部门',20,'二十部门','三十部门')
from emp;

如果comm列为空或者为0 返回 '无奖金' 否则返回 '有奖金'

select decode(comm,null,'无奖金',0,'无奖金','有奖金')
from emp;

八、nullif(参数1,参数2)

判断两个参数是否相同,相同返回空,不同返回参数1；参数数据类型必须一致，并且参1不能为空。

select nullif(1,1)  ,nullif(1,2)
from dual;