oracle 关联子查询以及伪列的使用

什么是关联子查询？

内查询的执行需要借助外查询，而外查询的执行又离不开内查询的执行，这时，内查询和外查询是相互关联的，这种子查询就被称为关联子查询。

在关联子查询中，内查询能够引用外部查询的列，对外部查询的每一行，内部查询都要执行一次。

---

关联子查询举例：

看一个例子：查询工资高于平均工资的员工信息。

select * from emp where sal>(select avg(sal) from emp);

上面就是一个简单的查询，并没有用到内查询必须依赖于外查询的关联子查询。

下面继续看一个例子：
查询工资高于“同职位”员工的平均工资的员工信息。

思路1：

查询工资高于同职位平均工资的员工信息
第一种思路：表连接方式
（1）先求各职位的平均工资
Select job,avg(sal) avgsal from emp group by job;
（2）将（1）的结果集看作一张表，与emp表连接
From emp,( Select job,avg(sal) avgsal from emp group by job) ae where emp.job=ae.job;
（3）两表连接后，搜索满足工资大于平均工资的员工
Where sal>avgsal

完整写法1：表连接的方式

select empno,ename,e.job,sal,avgsal from emp e,(select job,avg(sal) avgsal from emp group by job) ae where e.job=ae.job and sal>avgsal;


select * from emp,(select job,avg(sal) avgsal from emp group by job) ae where emp.job=ae.job and emp.sal>avgsal;

思路2：使用关联子查询

先写外部查询：
select empno,ename,job,sal from emp where sal>
再写子查询：各职位平均工资：
select job,avg(sal) avgsal from emp group by job
最后加条件：job=e.job

完整写法2:关联子查询

select * from emp e where sal > (select avg(sal) from emp where job = e.job);

内查询必须依赖于外查询的值，外查询也依赖于内查询。

对外部查询每执行一行，内查询就会执行一次。

例：查询所有工资高于自己部门平均工资的员工信息。

1、

select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) ae where emp.deptno=ae.deotno and emp.sal>avgsal;

2、

select * from emp e where sal > (select avg(sal) from emp where deptno= e.deptno);

---

联合查询/集合查询(不做详细说明，介绍一些伪表和伪列的使用)：

伪表：

所谓的伪表就是我们前面说讲到的dual表。

伪列：

不属于任何表，但是可以在任何表中直接使用。

rowid：用于表示地址
在数据表中，即使各行中所有列值都相同，rowid的值也不相同


rownum：主要作用是生成行号
对每一个结果集，其值都从1开始递增，直到末尾

看例子：

1、

select rownum,empno,ename,sal from emp;

2、

select rownum,empno,ename,sal from emp order by sal;

3、

select rownum,empno,deptno,sal from emp where deptno = 20;

通过1、2可以看出，2中的结果中的rownum是乱序的，因为order by把行的顺序打乱了，所以说明：rownum不是和行绑定的，而是和结果集绑定的。

使用rownum时只支持<、<=、和、!＝符号，
不支持>、>=、=和between…and符号

例：查找emp表中第3到第5条记录：

思路：
先求前5条记录
Select rownum,empno,ename from emp where rownum<=5;
把上面的查询结果作为一张表，这样rownum就可以当作一个普通字段使用，此时需要给该列起别名

完整写法：

select * from (select rownum r,empno,ename,sal from emp where rownum<=5) re where r>=3;

这个地方是将(select rownum r,empno,ename,sal from emp where rownum<=5)查询到的结果集作为一个表。

此处*所指的是子查询中的所有的列(rownum、empno、enamel、sal)


把rownum当作一个表中的普通字段的时候rownum才能够使用">="符号。

例：查找emp表中第3条记录：

select * from(select rownum r,e.* from emp) where r=3;

例：查找工资中最高的前3条记录：

select rownum,e.* from (select * from emp order by sal desc) e where rownum<=3;

思路就是：先进行排序，把排序后的表进行用rownum编号，从而保证前三条记录的rownum顺序不是乱序的。

---

例题：

查出每个员工的上级主管以及所在部门名称，并且主管薪水超过3000.
三表连接的问题.

select e.ename 员工,m.ename 领导,dname from emp e join dept d on e.deptno=d.deptno join emp m on m.empno=e.mgr where m.sal>3000;

emp e是一个员工表，emp m是一个领导表。

列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。
四表连接的问题.

select e.empno,e.ename,e.sal,dname,m.ename 领导,grade from emp e,dept d,emp m,salgrade s where e.deptno=d.deptno and e.mgr=m.empno and e.sal between losal and hisal and e.sal>(select avg(sal) from emp);

emp表：

dept表：

salgrade表：

bonus表：