oracle的分組查詢和連接配接查詢

分組函數:

六個常用的分組函數:

AVG,SUM,MIN,MAX,COUNT,WM_CONCAT: 行轉列      

PS:分組函數預設會自動過濾控制,可以使用NVL函數使分組函數無法忽略空值:

未使用NVL函數: select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三 from emp; --若有空記錄，第一個結果和二三結果的不同      

使用NVL函數: select count(*) 一, count(nvl(comm,0)) 二 from emp;      

分組函數的文法格式 :

PS:未包含在分組函數的列，必須要在group by子句後面

select a,b,c ，分組函數(d) from emp group by a,b,c;      

如何過濾分組資料: having 

PS: having與where的差別： where子句中不能使用分組函數,having必須和group by聯用

select deptno,avg(sal) from emp group by deptno having avg(sal) >4000      

order by

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

分組函數的嵌套：

select   max(avg(sal)) from emp group by  deptno;      

group by增強:

PS: group by rollup(a,b) 等價于  group by a,b +group by a, group by null

select deptno,job,sum(sal) from emp group by rollup(deptno,job);      

查詢結果如下：

10    CLERK        2689.2
10    MANAGER        4807.96
10    PRESIDENT    6084.64
10                13581.8
20    CLERK        5456.11
20    ANALYST        6736.6
20    MANAGER        5093.17
20                17285.88
30    CLERK        2728.04
30    MANAGER        5025.26
30    SALESMAN    11261.79
30                19015.09
                49882.77      

多表查詢：

笛卡爾積的概念：笛卡爾積就是列數相加，行數相乘

例如兩張表emp,dept的資料如下是：

笛卡爾積的資料的sql如下

select * from emp,dept      

結果如下：共56行資料

連接配接類型：

　　等值連接配接: e.deptno = d.deptno

　　不等值連接配接 : from emp e, salgrade s where e.sal > s.losal and e.sal < s.hisal

　　外連結 : 通過外連結，把對于連接配接條件不成立的記錄，任然包含在最後的結果中

　　　　左外連結：=(+)

　　　　右外連接配接： (+)=     : 下方的例子若不用右連接配接則查不出來兩邊表中不存在的deptno的記錄

右連接配接的作用: select d.deptno 部門号,d.dname 部門名稱,count(e.empno) 人數
            from emp e, dept d
            where e.deptno (+)=d.deptno
            group by d.deptno,d.dname;      

　　　　查詢結果如下：

　　　　若是不用右連接配接，sql語句中少了"(+)"，則查詢結果如下：

　　自連接配接:通過别名，将同一張表視為多張表

select e.ename 員工姓名, b.ename 老闆姓名
                from emp e ,emp b
                where e.mgr = b.empno      

　　　　自連接配接缺點：不适合操作大表，一張表做一次自連接配接 笛卡爾積會是記錄的平方；做兩次自連接配接笛卡爾積是記錄的立方

　　　　解決方法：層次查詢　　　

--上一層的員工号(prior empno)是目前層的老闆号(mgr) 

--level 僞列字段：表中不存在的字段 代表目前記錄的的層級      

select level,empno,ename,sal,mgr
from emp 
connect by prior empno = mgr
start with empno = 7839
order by 1;          

　　　　　　1)與自連接配接優點：是單表查詢，不會産生笛卡爾積

　　　　　　2)與自連接配接缺點：結果不直覺

　　　　自連接配接與層級查詢對比的優點：結果比較直覺