oracle表与表的关联 -电脑资料

    两个表之间进行的连接（99语法）

    select * from emp join dept on emp.deptno=dept.deptno;

    select * from emp join dept on dept.deptno=emp.deptno;

    等价于（92语法）

    select * from emp,dept where emp.deptno=dept.deptno;

    --左外连接 以右边为万能行 （确保左边的数据能在右边找到）

    select e1.ename,e1.empno ,e1.deptno from emp e1 left join emp e2 on e1.mgr=e2.empno;

    --右外连接 以左边为万能行

    select * from emp;

    select * from emp e1 right join emp e2 on e1.mgr=e2.empno;

    等价于

    select e1.ename,e1.empno ,e1.deptno from emp e1 left join emp e2 on e1.empno=e2.mgr;（左外连接）

    --全外连接 保证左边和右边的记录都被找到

    select * from emp e1 full join emp e2 on e1.mgr=e2.empno;

    92语法关于左外连接 在右边加上一个"+"

    select * from emp,dept where emp.deptno=dept.deptno（+）；

    反过来右外连接 在左边加上一个"+"

    select * from emp,dept where emp.deptno（+）=dept.deptno;

    -使用伪字段：rownum,----------------------

    ------------------------------------------------------------------------------------------

    --用来标识每条记录的行号，行号从1开始，每次递增1

    select a.* ,rownum from emp a;

    或者

    select emp.* ,rownum from emp ;

    --oracle下rownum只能使用 < <=, 不能使用 = > >= 等比较操作符，

    --当rownum和order by 一起使用时，会首先选出符合rownum条件的记录，然后再排序

    --例如，当我们要求薪水最高的前5个人时，最直接的想法可以这样写：

    select sal from emp order by sal desc;

    select ename ,sal from

    （select ename,sal from emp order by sal desc）

    where rownum<=5

    //取出薪水在排名在6-10的人

    select * from（

    select t.*,rownum r from（

    （select sal from emp order by sal desc）t

    ）where rownum<=10）

    where r>=6

    --------------------------------------------------------

    --不准用组函数（即MAX（）），求薪水的最高值（面试题）

    --第一种解决办法：

    --1,先把所有薪水按照倒序排列

    select sal from emp order by sal desc;

    --2,再取第一行

    select sal from

    （select sal from emp order by sal desc）

    where rownum=1

    [nbextpage]

    --第二种解决办法：

    思路：将同一个表复制成两份，要求某一个表中的sal小于另外一个表中的sal,只有工资最高的不可能小，

    再利用 not in 进行筛选从而起得最大值

    --1,先跨表查询自己，先求出的结果中，e1.sal不可能出现最大数

    select e1.sal from emp e1 join emp e2 on e1.sal

    --2,然后再not in

    select sal from emp where sal not in（select e1.sal from emp e1 join emp e2 on e1.sal

    -----------------------------------------------------------------

    --求平均薪水最高的部门的部门编号

    --第一种解决办法：

    --1,先求出每个部门的平均薪水，

    select deptno,avg（sal） from emp group by deptno;

    --2,再求每个部门的平均薪水的最高值，

    select max（avg_sal） from（select avg（sal） avg_sal from emp group by deptno）

    --3,最后再求第一步结果中avg_sal = 最高薪水的记录，

oracle表与表的关联

电脑资料

    select deptno,avg_sal from

    （select deptno,avg（sal） avg_sal from emp group by deptno）

    where avg_sal=

    （select max（avg_sal）from（select avg（sal） avg_sal from emp group by deptno） ）

    --这种写法没有考虑并列第一的情况

    select deptno from

    （select deptno,avg（sal） avg_sal from emp group by deptno order by avg（sal） desc）

    where rownum<=1

    --第二种解决办法：

    --1,将上面的第一步第二步合并，先求最高平均薪水，用max（avg（sal））的办法

    select max（avg（sal）） from emp group by deptno;

    --2,求出每个部门的平均薪水

    select deptno,avg（sal） from emp group by deptno;

    --3,最后再求第二步结果中（即每个部门的平均薪水），avg_sal = （第一步结果）的记录。即avg_sal =最高薪水的记录。

    select deptno,avg（sal） from emp group by deptno

    having avg（sal）=（select max（avg（sal）） from emp group by deptno）；

    --第三种解决办法：

    --1,先求出每个部门的平均薪水，

    select deptno,avg（sal） from emp group by deptno

    --2,求最高平均薪水，用max（avg（sal））的办法

    select max（avg（sal）） from emp group by deptno;

    --3,再使用having语句， avg（sal） = 第二步的结果

    --注意：为组函数起的别名在having中不能用

    select deptno,avg（sal） avg_sal from emp group by deptno

    having avg（sal）=（select max（avg（sal）） from emp group by deptno）