SQL-常用函数第六集-其余判断函数

41次阅读

共计 2510 个字符，预计需要花费 7 分钟才能阅读完成。

看完这章你会学会以下内容：

NVL 和 NVL2 语法和常用实例
DECODE 和 CASE WHEN 的区别

1.NVL 函数的格式如下：NVL(expr1,expr2)
含义：如果 oracle 第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

 SELECT NVL(NULL,0),     -- 空转 0
       NVL(NULL,100),   -- 空转 100
       NVL('', 99),     -- 空字符转 99
       NVL(10, 100),    -- 非空不会转
       NVL('AD',77)     -- 非空不会转
    FROM DUAL;

2.NVL2 函数的格式如下：NVL2(expr1,expr2, expr3)
含义：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

 SELECT NVL2(NULL, 0, 1),       -- 空转 1
       NVL2('', 99, 2),        -- 空字符转 2
       NVL2(10, 3, 100),       -- 非空转 3
       NVL2('AD', 'AD', 7)     -- 非空转 'AD'
    FROM DUAL;

3.应用实例：查看员工信息和其年薪（薪酬 + 奖金)

SELECT E.*,(SAL + NVL(COMM,0))*12 AS 年薪 FROM EMP E

4.应用实例二：对 EMP 表中的奖金为空的转换为 100, 不为空的转换为 NULL;

SELECT E.*,NVL2(COMM,NULL,100)AS 奖金 FROM EMP E;

3.DECODE(value,if1,then1,if2,then2,if3,then3,…,else) 含义：如果 value 与第一个值相等，则与第一个值的匹配值，以此类推，最后都没有的，就返回默认值。
如果有匹配值，则返回对应的匹配值。
如果没有匹配值，则返回空或者默认值。

IF 条件 = 值 1 THEN
RETURN(value 1)
ELSIF 条件 = 值 2 THEN
RETURN(value 2)
……
ELSIF 条件 = 值 n THEN
RETURN(value 3)
ELSE
RETURN(default)
END IF

 SELECT DECODE(12,    -- 原始值
           1, 2, 
          12, 10),    -- 10
       DECODE(12, 
           1, 2, 
          13, 10),    -- 空
       DECODE(12, 
           1, 2, 
          13, 10, 
           7),        -- 给定默认值 7
       DECODE(12, 
           1, 2, 
          12, 10,     -- 找到后就返回值, 然后退出, 不会向下找
          12, 88,
           7)   FROM DUAL;

3.1经典案例：用英文部门名称返回中文部门，然后匹配该部门下的人数

 -- ACCOUNTING   会计部
 -- RESEARCH     研究部
 -- SALES        销售部
 -- OPERATIONS   操作部
  
   SELECT DECODE(DNAME,
           'ACCOUNTING','会计部',
           'RESEARCH','市场调查部',
           'SALES','销售部',
           'OPERATIONS','操作部'
           ) AS 部门名称,
      COUNT(E.EMPNO) AS 部门人数
     FROM DEPT D
     LEFT JOIN EMP E
     ON E.DEPTNO = D.DEPTNO
    GROUP BY D.DNAME;

5. CASE WHEN 多条件判断
CASE [COLUMN_NAME] 语法：
WHEN [‘ 条件参数 ’] THEN [‘COLUMN_NAME/ 显示内容 ’]
WHEN [‘ 条件参数 ’] THEN [‘COLUMN_NAME/ 显示内容 ’]
……
ELSE [‘COLUMN_NAME/ 显示内容 ’]
END

5.1 经典案例：部门名称的转换用中文对应相应的中文
* 与 DECODE 函数等效，对单字段等值判断。

SELECT DEPT.*,
       (CASE
       WHEN DNAME = 'ACCOUNTING' THEN '会计部'
       WHEN DNAME = 'RESEARCH' THEN '研究部'
       WHEN DNAME = 'SALES' THEN '销售部'
       ELSE '操作部'
       END) AS 部门
    FROM DEPT;

5.2 经典案例：按照指定的条件，对薪酬划分等级。
* 与 DECODE 函数不同，可以对多字段匹配，而且不需要等值匹配。

SELECT E.*
      ,CASE WHEN SAL >=4000 THEN '富'
      WHEN SAL>=2500 THEN '良'
      WHEN SAL>=1000 THEN '中'
      ELSE  '穷'
      END AS salgrade 
      FROM EMP E

5.3 经典案例 更新数据：job 为 ’CLERK’ 转为 ’ 职员 ’, 将其工资增加 10%,

                           'SALESMAN' 转为 '销售员', 将其工资增加 5%, 
                           'MANAGER' 转为 '经理', 将其工资增加 2000, 
                           'ANALYST' 转为 '分析师', 将其工资增加 6%,
                           'PRESIDENT' 转为 '老板', 其工资不变;
            更新数据：update emp set job =(case when job='CLERK' then '职员'
                                    when job='SALESMAN' then '销售员'
                                    when job='MANAGER' then '经理'
                                    when job='ANALYST' then '分析师'
                                    when job='PRESIDENT' then '老板'
                                    else job
                                    end）as 岗位,
                           sal= (case when job='CLERK' then sal*(1+0.1)
                             when job='SALESMAN' then sal*(1+0.05)
                             when job='PRESIDENT' then sal+2000
                             when job='MANAGER' then sal*(1+0.06)
                             else sal 
                             end）as 工资
                         from emp;

   select emp.*,(case when job='CLERK' then '职员'
              when job='SALESMAN' then '销售员'
              when job='MANAGER' then '经理'
              when job='ANALYST' then '分析师'
              when job='PRESIDENT' then '老板'
              end）as 岗位,
          (case when job='CLERK' then sal*(1+0.1)
              when job='SALESMAN' then sal*(1+0.05)
              when job='PRESIDENT' then sal+2000
              when job='MANAGER' then sal*(1+0.06)
              when job='ANALYST' then sal
              end）as 工资
          from emp;

正文完