2021-08-09_小白的oracle 学习整理（七）nvl()、decode()、case when、pivote()

oracle 学习整理（七）

1、表连的部分应用

1.1 找出在A表中存在，在B表中不存在的账号是多少？

select id from A
minus
select id from B;

select A.id from A left join B on A.id=B.id
where B.id is null;

1.2 查询SMITH和他的上班地点:

select ename,loc from scott.emp a,scott.dept b 
where a.deptno=b.deptno and ename='SMITH';

嵌套
查询SMITH和他的上班地点：  --56
select ename,loc from scott.emp a,scott.dept b 
where a.deptno=b.deptno and ename='SMITH';

1.3 表连接注意事项

  1 在表接连的时候，需要先对表格进行数据的筛选，对筛选的结果再进行表的联合查询，
	这样可以减少笛卡尔积的出现，增加查询的效率。
  2 首先先看这个句子能不能用嵌套的子查询来实现，如果查询的结果都在一个表里面，
	就可以使用子查询，如果跨了多个表格，就一定是表连接，表连接也要先筛选在连接。

2 带逻辑的查询方法

2.1 设置空值的默认值：nvl(列名, 默认值)

查询emp每一个员工，工资和奖金的总和是多少？
select ename,sal+nvl(comm,0) from scott.emp;
练习：判断员工的上级领导的基本信息，如果这个人没有上级领导，领导编号就默认成自己。
select empno,nvl(mgr,empno) from scott.emp;

2.2 对空值所在的列，进行空值和非空值的判断：nvl2(列名, 如果不为空, 如果为空)

判断员工有没有领导编号，如果有，打印'员工'，如果没有，打印'老板'。
select empno,nvl2(mgr,'员工','老板') from scott.emp;

查询员工编号、工资，奖金，工资和奖金的和
select empno,sal,comm,
       nvl2(comm,sal+comm,sal)
  from scott.emp;

2.3 对所有的空值或者精确的内容进行判断：decode(列名, 条件判断, 判断为真, 判断为假)

对comm是否为空进行判断，打印出‘有奖金’和‘没有奖金’ 的对应提示。

判断comm是否为空，如果为空打印没有奖金，再次判断是否等于0，如果等于0也是没有奖金，剩下的情况打印有奖金。
select empno,comm,decode(comm,null,'没有奖金',0,'没有奖金','有奖金') 
from scott.emp;

判断员工的岗位信息，如果是MANAGER或者是PRESIDENT，打印'管理层'，否则就打印‘普通员工’。

判断员工的工资，如果工资小于等于2000，打印C，2001到3000就打印B，3001以上就打印A。
decode()本身是不能进行范围判断的，只能进行精确内容的判断，如果要判断范围，需要加上sign()这个方法：
sign(值-边界内容)：如果值小于边界，返回-1；等于边界返回0；大于边界返回1。

select sal,
decode(sign(sal-2001)+sign(sal-3000),'-2','C','2','A','B')
from scott.emp;

2.4 判断逻辑的基本句型：case when

case
    when 条件判断1  then  执行结果
    when 条件判断2  then  执行结果
    ...
    when 条件判断n  then  执行结果
    else  执行结果
end

判断员工的工资，如果工资小于等于2000，打印C，2001到3000就打印B，3001以上就打印A。
select empno,
       case 
         when sal<=2000 then 'C'
         when sal between 2001 and 3000 then 'B'
         else 'A'
       end
  from scott.emp;  
查询sc这个表格里面，及格和不及格的人数，分别是多少？
select s,count(1) from
(select sno,
       case
         when score<60 then '不及格'
         else '及格'
       end s
  from sc) a
  group by s; 

判断emp表格里面，谁是有奖金的，谁是没有奖金的
select empno,
       case
         when comm>0 then '有奖金'
         else '没有奖金'
       end
  from scott.emp;

2.5 列到行的数据转换：pivot()

select * from (对原表的查询，找出在转换过程中需要的列的名字)
pivot(聚合函数() for 分组的列 in (分组的列的内容));

用列转行的方式，查看每个部门的平均工资：
select * from (select deptno,sal from scott.emp)
pivot(avg(sal) for deptno in (10,20,30));

用列转行的方式，查询出每个部门分别有几个人：
select * from (select deptno,empno from scott.emp)
pivot(count(empno) for deptno in (10,20,30));

统计每个部门中，每个不同的工作岗位，分别有几个人？
deptno	CLERK   MANAGER   PRESIDENT  ANALYST  SALESMAN
10		1	      1          1
20      2         1                    2
30      1         1                    				4

select * from (select deptno,job,empno from scott.emp)
pivot(count(empno) for job in('CLERK','MANAGER','PRESIDENT','ANALYST','SALESMAN'))
order by deptno;