5—多表查询.docVIP

多表的查询 多表查询的条件是不能少于表的个数减一。【笛卡儿集】 上图答案： select a1.ename ,a1.sal, a2.dname from emp a1 ,dept a2 where a1.deptno = a2.deptno; select a2.dname, a1.ename, a1.sal from dept a2,emp a1 where a1.deptno = a2.deptno and a1.deptno = 10; select a.ename, a.sal, b.grade from emp a, salgrade b where a.sal between b.losal and b.hisal; 扩展要求答案： select a1.ename ,a2.dname, a1.sal from emp a1,dept a2 where a1.deptno = a2.deptno order by a1.deptno; select worker.ename ,boss.ename from emp worker ,emp boss where worker.mgr = boss.empno; select worker.ename ,boss.ename 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 ename ,sal, deptno from emp where sal (select max(sal) from emp where deptno = 30); select * from emp where ( deptno,job) = (select deptno ,job from emp where ename = SMITH); select a1.ename ,a1.sal,a1.deptno,a2.平均工资 from emp a1,(select deptno ,avg(sal) 平均工资 from emp group by deptno) a2 where a1.deptno = a2.deptno and a1.sal a2.平均工资; 解析：第一步：查询出各个部门的平均工资和部门号。 select deptno ,avg(sal) 平均工资 from emp group by deptno; 第二步：把上面的查询看成一个表。子表。 select a1.ename ,a1.sal,a1.deptno,a2.平均工资 from emp a1,(select deptno ,avg(sal) 平均工资 from emp group by deptno) a2 where a1.deptno = a2.deptno and a1.sal a2.平均工资; Oracle的分页查询一共有三种方式。 4、查询的几个变化： a、指定查询列，只需修改最里层的子查询。 b、如何排序？只需修改最里层的子查询。 下面的不是很重要知道就好