几个判断赋值函数

NVL(参数1,参数2):判断参数1是否为空。如果不为空，则取参数1的值，如果为空，则取参数2的值
                 参数1是目标字段，参数2是一个具体的值
--需要注意：
参数2的值的类型要跟参数1的类型一致
--比如:
SELECT E.*,NVL(E.COMM,0) FROM EMP E;

NVL2(参数1,参数2,参数3)：判断参数1是否为空。如果不为空，则取参数2的值，如果为空，则取参数3的值 
                         参数1是目标字段，参数2和参数3是一个具体的值
--需要注意的是：
参数2的值的类型要跟参数3的类型要一致, 可以跟 参数1的类型不一致（只是判断目标字段到底是否为空）                     
--比如:
SELECT E.*,NVL2(E.COMM,1,0) 
FROM EMP E;

CASE WHEN 判断
--语法1结构：
CASE 要比较的字段
    WHEN 值1 THEN 结果1
  [WHEN 值2 THEN 结果2
  .
  .
  .
  ELSE 其他结果]
END

--需要注意的是：
1、位置是在 SELECT 后面作为要查询的信息
2、会生成一个新的列，一般给这个列取个别名
3、最后的 END 不要忘
4、ELSE 不要忘，除非你能列出所有的条件。否则这个新生成的列里面会有空值
5、语法1只能对 单个字段 进行 等值 判断

--比如：查询emp表中的员工的职位，并显示中文
SELECT E.*,
       CASE E.JOB
         WHEN 'CLERK' THEN '办事员'
         WHEN 'SALESMAN' THEN '销售'
         WHEN 'MANAGER' THEN '经理'
         WHEN 'ANALYST' THEN '分析员'
         --ELSE '总裁'
         --WHEN 'PRESIDENT' THEN '总裁'
       END AS "中文职称"
FROM EMP E;

/*小练习一把：查询EMP表中的员工的信息，根据部门编号给员工加薪，
                部门10的加10000
                部门20的加5000
                部门30的加50%
          显示员工的信息以及加薪后的工资*/
SELECT E.*,
       CASE E.DEPTNO
         WHEN 10 THEN E.SAL + 10000
         WHEN 20 THEN E.SAL + 5000
         WHEN 30 THEN E.SAL * 1.5
       END NEW_SAL
FROM EMP E;

--语法2结构
CASE
  WHEN 条件1.1 [AND/OR 条件1.2] THEN 结果1
  [WHEN 条件2.1 [AND/OR 条件2.2] THEN 结果2
  .
  .
  .
  ELSE 其他结果]
END

--需要注意的是：
1、不仅可以做等值（=）判断，也可以做非等值（!= 、<> 、>、 >=、 < 、<= 、IN、 BETWEEN AND）判断
2、可以对多个字段分别进行判断，但是需要注意，两个字段得到的结果不能有交集，否则，会以最先的那个判断为准

--比如：查询emp表中的员工的职位，并显示中文
SELECT E.*,
       CASE
        WHEN E.JOB = 'CLERK' THEN '办事员' 
        WHEN E.ENAME = 'SMITH'  THEN '姓名asrfsrsfd'
         WHEN E.JOB = 'SALESMAN' THEN '销售'
         WHEN E.JOB = 'MANAGER' THEN '经理'
         WHEN E.JOB = 'ANALYST' THEN '分析员'
         ELSE '总裁'
       END "中文职称"
FROM EMP E;

/*小练习一把：查询emp表中员工的信息，给员工加薪
              工资大于2000的，奖金加5000
              工资大于1000的，奖金加8000
              其他的，奖金加20000
      查询员工的信息，以及加薪后的奖金*/ 
SELECT E.*,
       CASE
         WHEN E.SAL > 2000 THEN NVL(E.COMM,0) + 5000
         WHEN E.SAL > 1000 AND E.SAL <= 2000 THEN NVL(E.COMM,0) + 8000
         ELSE NVL(E.COMM,0) + 20000
       END NEW_COMM
FROM EMP E;

WHEN E.JOB = 'CLERK' THEN '办事员' 

--小练习一把：查询部门员工的工资高于该部门的平均工资的员工信息及部门平均工资

SELECT F.*
FROM (SELECT E.*,
           AVG(E.SAL)OVER(PARTITION BY E.DEPTON) DEPT_AVG
           FROM EMP E ) F
WHERE F.SAL >F.DEPT_AVG
查询各个工资等级的平均工资以及员工的信息以及工资等级
SELECT E.*,
FROM SLAERGRATE
       SUM(E.SAL) OVER(PARTITION BY S. ORDER BY E.HIREDATE ASC) DEPT_HIRE_SAL
  FROM EMP E;
  
  SELECT F.*
FROM (
SELECT E.*,
       AVG(SAL)OVER(PARTITION BY E.DEPTNO) DEPT_AVG
FROM EMP E) F
WHERE F.SAL > F.DEPT_AVG;

-小练习一把：分别用三种函数查出各个部门的工资排名
SELECT E.*,
       ROW_NUMBER()OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL ASC) RN,
       RANK()OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL ASC) RK,
       DENSE_RANK()OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL ASC) DR
FROM EMP E;

 SELECT E.*,
       SUM(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.HIREDATE ASC) DEPT_HIRE_SAL
  FROM EMP E;EMP E;

--小练习一把：查询emp表中各个部门的员工的前一个入职的员工的工资，后一个入职的员工的工资
SELECT E.*,
       LAG(E.SAL, 1, 0) OVER(ORDER BY E.DEPTNO) LAG_SAL,
       LEAD(E.SAL, 1, 0) OVER(ORDER BY E.DEPTNO) LAG_SAL
  FROM EMP E;

SELECT E.*
 LAG(E.SAL,1,0)OVER(ORDER BY E.)
FROM EMP 

CREATE TABLE S (Y NUMBER(4),Q NUMBER (4),AMT NUMBER(4));

INSERT INTO S VALUES (2015,1,100);
INSERT INTO S VALUES (2015,2,100);
INSERT INTO S VALUES (2015,3,100);
INSERT INTO S VALUES (2015,4,100);
INSERT INTO S VALUES (2016,1,100);
INSERT INTO S VALUES (2016,2,100);
INSERT INTO S VALUES (2016,3,100);
INSERT INTO S VALUES (2016,4,100);
COMMIT;

SELECT S.*,
  LEAD (S.AMT,1)OVER(PARTITION BY S.Y ORDER BY S.Q) QQ,
  LEAD (S.AMT,2)OVER(PARTITION BY S.Y ORDER BY S.Q) WW,
  LEAD (S.AMT,3)OVER(PARTITION BY S.Y ORDER BY S.Q) EE
  FROM S) SS
  WHERE SS.Q=1;