子查询
子查询就是在一个完整的查询之中定义了若干个小的查询形成复杂查询，
可是所有的子查询编写的时候一定要使用（）标记。
子查询返回结果分为四类：
单行单列：返回一个具体列的内容，可以理解为一个单值数据；
单行多列：返回一行数据中多个列的内容；
多行单列：返回多行记录中同一列的内容，相当于给出了一个操作范围；
多行多列：查询返回的结果是一张临时表；
何处使用子查询参考建议：
where子句：此时子查询返回的结果一般都是单行单列，单行多列、多行单列；
having子句：此时子查询返回的都是单行单列数据，同时为了使用统计函数操作
from子句：此时子查询返回的结果一般都是多行多列，可以按照一张数据表（临时表）的形式操作。
1.在where子句中使用子查询
1.1子查询返回单行单列数据
范例：
查询公司之中工资最低的雇员的完整信息
select * from emp where sal=(
select min(sal) from emp);
select * from emp;
1.2 子查询返回单行多列数据
范例:
查询与MARTIN从事同一工作且工资相同的雇员信息
select * from emp where (job,sal)=
(select job,sal from emp where ename='MARTIN')and ename<>'MARTIN';
查询与雇员7566从事同一工作且领导相同的全部雇员信息
select * from emp where (job,mgr)=
(select job,mgr from emp where empno=7566)and empno<>7566;
范例：
查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息（包括ALLEN）
select * from emp where (job,to_char(hiredate,'yyyy'))=
(select job,to_char(hiredate,'yyyy') from emp where ename='ALLEN')
1.3子查询返回多行单列数据
在使用多行子查询是，主要使用三种操作符:in ,any,all
1.3.1 in操作符
范例：查询出与每个部门中最低工资相同的全部雇员信息。
select * from emp where sal in(
select min(sal)from emp group by deptno);
范例：查询出与每个部门中不与最低工资相同的全部雇员信息
select * from emp where sal not in(
select min(sal)from emp group by deptno);
注意：如果在IN之中子查询返回数据有null,不会有影响。如果在 NOT IN之中子查询返回数据有null，
就表示不会有任何数据返回。
例：select * from emp where mgr not in
(select mgr from emp where ename='KING');
1.3.2 ANY 操作符
ANY 在使用中有如下三种形式：
=ANY：表示与子查询中的每个元素进行比较，功能与IN类似（然而<>ANY 不等价与not in)
>ANY:比子查询中返回结果的最小的要大（还包含了>=ANY）
select * from emp where sal >any(
select min(sal) from emp group by deptno);
select * from emp where sal
select min(sal) from emp group by deptno);
1.3.3ALL 在使用中有如下三种形式：
<>ALL：等价于NOT IN（然而=ALL不等价与in)
>ALL:比子查询中返回结果的最大的要大（还包含了>=ALL）
select * from emp where sal >all(
select min(sal) from emp group by deptno);
select * from emp where sal
select min(sal) from emp group by deptno);
空数据判断：
在sql之中提供了一个exists结构用于判断子查询是否有数据返回。如果子查询中有数据返回，
则exists返回true,反之返回false。
select * from emp where exists(
select * from emp where empno=9999)
select * from emp where not exists(
select * from emp where empno=9999)
小结：
where 子句可以判断单个数值、多个数值；
使用in,any,all可以处理多行单列子查询。
利用existske可以判断查询结果是否为null。
2.在having子句中使用子查询。
having一定结合group by 子句一起使用，其主要目的是进行分组后数据再次进行过滤，而且与where子句
不同的是，having是在分组后，可以使用统计函数。
范例：查询部门编号、雇员人数、平均工资、并且要求这些部门的平均工资高于公司平均薪金。
select deptno,count(deptno),avg(sal)as deptavg from emp group by deptno
having avg(sal) >(select avg(sal)from emp);
范例：查询出每个部门平均工资最高的部门名称及平均工资
select d.dname,avg(e.sal)from emp e,dept d
where e.deptno=d.deptno
group by d.dname having avg(sal)=(
select max(avg(sal))from emp group by deptno)
;
3.在from子句中使用子查询。
范例：要求查询出每个部门的编号、名称、位置、部门人数、平均工资。
实现方法一：多字段分组
select d.deptno,d.dname,d.loc,count(e.deptno),round(avg(e.sal),2)
from dept d,emp e
where d.deptno=e.deptno
group by d.deptno,d.dname,d.loc
;
实现方法二:子查询实现
select d.deptno,d.dname,d.loc,temp.人数,temp.平均工资
from dept d,
(select deptno 部门编号, count(deptno) 人数,round(avg(sal),2) 平均工资 from emp
group by deptno) temp
where d.deptno=temp.部门编号;
注：两种操作都能实现同样的结果。但是执行效率不同。例如将数据量扩大100倍。
emp表中记录为1200条，dept表中记录为300条。
实现一：多字段分组实现
直接关联emp跟dept表，就存在笛卡尔积
1400*300=420000
实现二：子查询
统计：emp 表的1400条记录，而且最终的统计记过的行数不可能超过300行（部门就300条）
多表关联：dept表的300条*子查询的最多300条=90000
总计：90000+300=90300条 效率相差一个数量级。
范例：
查询出所有在部门sales工作的员工的编号、姓名、基本工资、奖金、职位、雇佣日期、部门的最高工资
和最低工资
select e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,saltemp.msal,saltemp.misal
from emp e ,
(select deptno dno,max(sal) msal,min(sal) misal from emp group by deptno) saltemp
where e.deptno=saltemp.dno
and deptno=(select deptno from dept where dname='SALES');
范例：
查询出所有薪金高于公司平均薪金的员工编号、姓名、基本工资、职位、雇用日期，所有在部门名称
位置，上级领导姓名，公司的工资等级，部门人数、平均工资、平均服务年限。
select e1.deptno,e1.empno,e1.ename,e1.sal,e1.job,e1.hiredate,e2.ename,temp2.count,temp2.sal,temp2.nian,
d.dname,d.loc,s.grade
from emp e1,emp e2,dept d,salgrade s,
(select deptno dno,count(empno) count,round(avg(sal),2) sal ,round(avg(months_between(sysdate,hiredate)/12)) nian
from emp group by deptno) temp2
where e1.mgr=e2.empno and
e1.sal>(select avg(sal) from emp) and
e1.deptno=d.deptno and
e1.sal between s.losal and s.hisal and
e1.deptno=temp2.dno;
范例：列出总薪金比ALLEN或CLARK多的所有员工编号、姓名、基本工资、部门名称、其领导姓名，部门人数;
select e.empno,e.ename,e.sal,d.dname,m.ename,temp.count
from emp e ,dept d,emp m,
(select deptno dno,count(empno) count from emp group by deptno ) temp
where e.mgr=m.empno(+)
and e.deptno=d.deptno
and e.deptno=temp.dno
and (nvl2(e.comm,e.sal+e.comm,e.sal)>
any(select nvl2(comm,sal+comm,sal) from emp where ename in('ALLEN','CLARK')));
范例：列出公司各个部门的经理（假设每个部门只有一个经理，job为manager)的姓名、薪金、部门名称
部门人数、部门平均工资。
select e.ename,e.sal,d.dname,temp.count,temp.sal
from emp e,dept d,
(select deptno dno,count(deptno) count,round(avg(sal),2) sal from emp group by deptno)temp
where e.deptno = d.deptno
and e.job='MANAGER'
and e.deptno=temp.dno;
总结：from子句出现的子查询返回结果为多行多列
利用子查询可以解决多表查询所带来的性能问题。
4.在select子句中使用子查询
自查询可以出现在任意的位置上，不过从实际的项目来讲，在where，from，having之中使用子查询的情况比较多
而对于select子句，只能是以一种介绍的形式进行说明。
范例：查询出公司每个部门的编号、名称、位置、部门人数、平均工资。
方法一：通过from子句实现
select d.deptno,d.dname,d.loc,temp.count,temp.avg
from dept d ,
(select deptno dno,count(deptno) count,round(avg(sal),2) avg
from emp group by deptno) temp
where d.deptno=temp.dno(+)
方法二：通过select子句实现
select d.deptno ,d.dname,d.loc,
(select count(empno) from emp where deptno=d.deptno group by deptno) count,
(select avg(sal) from emp where deptno=d.deptno group by deptno) avg
from dept d;
5.子查询：with子句
临时表就是查询结果，如果一个查询结果返回结果为多行多列，那么就可以将其定义在From子句之中，
表示其为一张临时表。除了在FROM子句之中出现临时表之外，也可以利用with子句直接定义临时表。
范例：使用with子句将emp表中的数据定义为临时表
with e as(
select * from emp)
select * from e;
范例：查询每个部门的编号、名称、位置、部门平均工资、人数
with e as(
select deptno dno,count(deptno) count,round(avg(sal),2) avg from emp group by deptno)
select d.deptno,d.dname,d.loc ,e.avg,e.count from dept d,e
where d.deptno=e.dno(+)
6.子查询：分析函数
虽然利用SQL之中提供的各种查询命令可以完成大部分的查询要求，但是还有许多功能是无法
实现的，例如：
计算运行总量：逐一累加当前行与其之前行的每行记录数据；
查询当前行数据占总数据的百分比；
分区显示：按照不同的部门或职位进行排列、统计；
计算流动数据行的平均值等。
3.1分析函数的基本语法：
函数名称over([参数,...])(partition by 子句 字段，...
[order by 子句 字段,...][asc|desc][nullsfirst|nullslast]]
[windowing子句]);
本语法组成如下：
函数名称：类似于统计函数（count(),sum()等）,但是在此时有了更多的函数支持；
over子句：为分析函数指明一个查询结果集，此语句在select子句之中使用；
partition by 子句：将一个简单的结果集分为N组（或称为分区），而后按照不同的组
对数据进行统计；
order by 子句：明确指明数据在每个组内的排列顺序，分析函数的结果与排列顺序有关
nullsfirst|nullslast:表示返回数据行中包含null值是出现在排序列前还是尾
windowing子句：给出在定义变化的固定的数据窗口方法，分析函数将对此数据进行操作。
范例：使用partition语句
select deptno,ename,sal
from emp;
这只是一个简单的查询，但是这个select 子句里面是不可能出现统计函数的，如（count(),sum()）。
但是使用分析函数partition语句就可以实现。
select deptno,ename,sal,sum(sal) over(partition by deptno)
from emp;
如果没有分区，那么会把所有的数据当成一个区，一起进行统计。如下：
select deptno,ename,sal,sum(sal) over() from emp;
范例：使用partition by 设置多个分区。
select deptno,ename,sal,job,sum(sal) over(partition by deptno,job) sum
from emp;
范例：观察order by 子句
select deptno,ename,sal,rank() over(partition by deptno order by sal desc)rk
from emp;
--rank()函数生成序号。
将上面的例子设置多个排序字段
select deptno,ename,sal,hiredate,rank() over(partition by deptno order by sal ,hiredate desc)rk
from emp;
如果现在不写分区操作，那么就表示所有数据排序。
直接利用order by 排序所有数据。
select deptno,ename,sal,hiredate,sum(sal) over(order by ename asc)rk
from emp;
在order by 子句之中还存在两个选项：nulls first和nulls last。
其中nulls first表示在进行排序前，出现null值的数据行排列在最前面，
而nulls last 则表示出现的null值数据行排列在最后面。
select deptno ,ename,sal,comm,
rank() over(order by comm desc nulls last) rk,
sum(sal) over(order by comm desc nulls last)sum
from emp
windowing子句
分窗子句主要是用于定义一个变化或固定的数据窗口方法，定义分析函数在操作行的集合，分窗子句有两种
实现方式：
实现一：值域窗（range window）,逻辑偏移。当前分区之中当前的行的前N行到当前行的记录集
实现二：行窗（rows window）,物理偏移。以前排序的结果顺序计算偏移当前行的起始记录集。
而如果要想制定range或rows的偏移量，则可以采用如下几种排序列：
range|rows 数字 preceding;
range|rows between unbounded preceding and current row;
range|rows between current row and unbounded following;
以上的几种排列之中包含的概念如下：
preceding ,主要是设置一个偏移量，这个偏移量可以使用户设置的数字，或者是其他标记
between..and :设置一个偏移量的操作范围
unbounded preceding:不限制偏移量的大小
current row:表示当前行
following：如果不写此语句表示使用上N行与当前行指定数据比较，如果编写此语句，表示
当前行与下N行数据比较。
范例：验证range子句
range子句设置的是一个逻辑的偏移量
在sal上使用偏移量
select deptno,ename,sal,
sum(sal) over (partition by deptno order by sal range 300 preceding)sum
from emp;
子查询未学完、、、
