oracle表與表的關(guān)聯(lián) -電腦資料

    兩個(gè)表之間進(jìn)行的連接（99語法）

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

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

    等價(jià)于（92語法）

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

    --左外連接 以右邊為萬能行 （確保左邊的數(shù)據(jù)能在右邊找到）

    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;

    等價(jià)于

    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語法關(guān)于左外連接 在右邊加上一個(gè)"+"

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

    反過來右外連接 在左邊加上一個(gè)"+"

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

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

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

    --用來標(biāo)識(shí)每條記錄的行號(hào)，行號(hào)從1開始，每次遞增1

    select a.* ,rownum from emp a;

    或者

    select emp.* ,rownum from emp ;

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

    --當(dāng)rownum和order by 一起使用時(shí)，會(huì)首先選出符合rownum條件的記錄，然后再排序

    --例如，當(dāng)我們要求薪水最高的前5個(gè)人時(shí)，最直接的想法可以這樣寫：

    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

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

    --不準(zhǔn)用組函數(shù)（即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]

    --第二種解決辦法：

    思路：將同一個(gè)表復(fù)制成兩份，要求某一個(gè)表中的sal小于另外一個(gè)表中的sal,只有工資最高的不可能小，

    再利用 not in 進(jìn)行篩選從而起得最大值

    --1,先跨表查詢自己，先求出的結(jié)果中，e1.sal不可能出現(xiàn)最大數(shù)

    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

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

    --求平均薪水最高的部門的部門編號(hào)

    --第一種解決辦法：

    --1,先求出每個(gè)部門的平均薪水，

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

    --2,再求每個(gè)部門的平均薪水的最高值，

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

    --3,最后再求第一步結(jié)果中avg_sal = 最高薪水的記錄，

oracle表與表的關(guān)聯(lián)

電腦資料

    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,求出每個(gè)部門的平均薪水

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

    --3,最后再求第二步結(jié)果中（即每個(gè)部門的平均薪水），avg_sal = （第一步結(jié)果）的記錄。即avg_sal =最高薪水的記錄。

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

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

    --第三種解決辦法：

    --1,先求出每個(gè)部門的平均薪水，

    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） = 第二步的結(jié)果

    --注意：為組函數(shù)起的別名在having中不能用

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

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