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

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

• 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;