oracle多表查询，基于scott下的四表为例

多表查询　

显示部门号为１０的雇员名，雇员工资及所在部门的名字

select a1.ename,a1.sal,a2.dname a1.deptno from emp a1,dept a2 where a1.deptno=10 and a2.deptno=10;

示例２０：显示用户姓名、工资和工资级别

select a1.grade,a2.ename,a2.sal from salgrade a1 ,emp a2 where a2.sal between a1.losal and a1.hisal;

示例２１：

显示员工名　，工资，所在部门名并按部门排序

 select a1.ename,a1.sal,a2.dname from emp a1 ,dept a2 where a1.deptno=a2.deptno order by a1.deptno;

自连接：

示例２２：

显示ＦＯＲＤ的上级

 select worker.ename ,boss.ename,worker.job from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='FORD';

示例２３：

显示和ＳＭＩＴＨ同一部门的所有员工

select *from emp where deptno=(select deptno from emp where ename ='SMITH')

多行子查询

查询和１０号部门工作相同的员工

select *from emp where job in(select distinct job from emp where deptno=10)

示例２４：

查询比３０号部门所有员工工资都高的员工信息

 select *from emp where sal>all(select sal from emp where deptno=30);

示例２５：

查询比３０号部门任意一个员工工资高的员工

 select *from emp where sal>any(select sal from emp where deptno=30);

多列子查询　

查询和ＳＭＩＴＨ部门和岗位都相同的员工

select *from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

如何显示高于自己部门平均工资的员工信息

 select *from emp a1 where sal> (select avg(sal) from emp a2 where  a1.deptno=a2.deptno group by deptno ) ;

解法2：

1、把下面的查询当做一张子表，查找到的结果为部门编号和平均工资，给平均工资加一个别名，方便以后调用

内嵌视图

select deptno ,avg(sal) mysvg from emp group by deptno)

2、select *from emp  a2 ,(select deptno ,avg(sal) mysvg from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysvg;

分页：

1.select a1.* ,rownum rw from (select *from emp) a1;

显示rownum(oracle分配的)

示例26：挑选6-10的记录

1、先建立子查询，查找1-10的记录

 select a1.* ,rownum rw from (select *from emp) a1 where rownum <=10;

2、把子查询当作一个表，查询6-10记录

select *from (select a1.* ,rownum rw from (select *from emp) a1 where rownum <=10) where rw>=6;

注：如果要修改查询列，只需要修改最里层的查询语句

 select *from (select a1.* ,rownum rw from (select ename,sal from emp) a1 where rownum <=10) where rw>=6;

select *from (select a1.* ,rownum rw from (select ename,sal from emp order by sal) a1 where rownum <=10) where rw>=6;

示例27：

用查询结果创建新表

create table myteble (empno,ename,sal) as select empno ,ename,sal from emp;

union合并查询:

查询结果为并集

union all

不会取消重复记录

intersect 

取交集

minus

取差集