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

野性酷女 2024-03-22 11:34 39阅读 0赞

**目录**

一、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;

![3d1a1650a5b743ada8de101eeacb0085.png][]

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

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

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

![6362e9373e1b4f39afccf409d63c1995.png][]

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

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

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

![7099004fbf104a839e051302afa0ad61.png][]

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

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;

![c83f6282bf6a4458b3db30e102123690.png][]

[3d1a1650a5b743ada8de101eeacb0085.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/ab057526374245e4ac71c4c76e1c2489.png
[6362e9373e1b4f39afccf409d63c1995.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/4577f5552c5241189b671ffd07a9176f.png
[7099004fbf104a839e051302afa0ad61.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/4f9091bfccd34ce7a790793b56a26996.png
[c83f6282bf6a4458b3db30e102123690.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/a843734749b24687a95467370bb8ffe4.png