一起聊聊Oracle高階查詢（範例詳解）

Oracle高階查詢

高階查詢在資料庫的開發過程中應用廣泛，從分組查詢、多表查詢和子查詢三個方面介紹Oracle的高階查詢。

分組查詢

分組查詢是按照一定的規則進行分組，分組以後資料會聚合，需要使用聚合函數，但是使用聚合函數不一定要分組，分組的關鍵字是group by。

常用的聚合函數有：最大值max()，最小值min()，平均值avg()，總和sum()，統計個數count()

count函數使用列名時會自動忽略空值

nvl函數可以防止count自動忽略空值，它的作用是當comm為空時返回0，因為0是非空，所以會進入統計總數。

group by子查詢

在select 列表中所有未包含在聚合函數中的列都應該包含在group by子句中。

單列分組

求每個部門的平均工資，顯示部門號，部門的平均工資。

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

多列分組

按部門，不同職位，統計員工的工資總和

select detpno,job,sum(sal) from emp group by deptno,job order by deptno

過濾分組

having子句的使用

where與having的區別

• where子句中不能使用聚合函數，先過濾後分組
• having子句中可以使用聚合函數，先分組後過濾

注意：從SQL優化的角度上看，儘量使用where，因為where使得分組記錄數大大降低，從而提高效率。

求平均工資大於2000的部門

select deptno,avg(sal) from emp where(avg(sal)) > 2000 group by deptno

where子句中不能使用聚合函數，所以報錯，改成having xxx子句即可。

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

在分組查詢中使用order by

求每個部門的平均工資，顯示部門號，部門的平均工資，按照工資升序排列。

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

也可以按列的別名排序

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

還可以按列的序號排序，平均工資是第2列

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

降序排列加上desc即可

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

分組函數的巢狀

求部門平均工資的最大值

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

group by語句增強

主要用在group by語句報表功能

每個部門，安裝不同職位，求工資總和，部門小結，總結。

可以使用rollup函數

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

再設定一下顯示格式，break on deptno表示相同的部門號只顯示一個，skip 1表示不同的部門號之間空1行。

完善報表顯示

增加標題，頁碼等

ttitle col 15 ‘我的報表’ col 35 sql.pno

設定標題，空15列顯示我的報表，然後空35列顯示頁碼

col deptno heading 部門號

col job heading 職位

col sum(sal) heading 工資總額

以上3行設定列標題

break on deptno skip 1

設定顯示格式，相同的部門號只顯示一個，不同部門號之間空1行

將這些設定儲存到一個sql檔案（注意要改成ANSI編碼，否則會出現亂碼並且設定無效），然後通過get命令讀取執行。再次執行查詢語句，得到如下報表。如果出現了多頁，為了顯示美觀，可以設定一頁顯示更多的行，比如設定每頁顯示100行：set pagesize 100

多表查詢

上面的例子都是從單個表中查詢資料，下面開始講解從多個表中查詢資料。

為了避免笛卡爾集，可以在where加入有效的連線條件，在實際允許環境下，應避免使用笛卡爾全集。

等值連線

範例：查詢員工資訊，要求顯示：員工號，姓名，月薪，部門名稱

需要查詢員工表和部門表，通過部門號進行等值連線查詢，where xxx=xxx

select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno

不等值連線

範例：查詢員工資訊，要求顯示：員工號，姓名，月薪，薪水級別

需要查詢員工表和薪水等級表，通過薪水等級上下限進行不等值連線查詢。where xxx between xxx and xxx，注意：小值在between前面，大值在between後面

select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal

外連線

範例：按部門統計員工人數，要求顯示：部門號，部門名稱，人數

需要查詢部門表和員工表

以下是通過等值連線的方式查詢，雖然總人數沒有問題，但是少了一個部門，因為一個部門沒有員工。

select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數 from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname

外連線一般通過join來實現，一張圖看懂SQL的各種join用法。

使用join語句重新實現範例功能

select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數 from dept d left join emp e on d.deptno=e.deptno group by d.deptno,d.dname

自連線

範例：查詢員工姓名和員工的老闆姓名

核心：通過別名，將同一張表視為多張表

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

這種方式會產生笛卡爾集，不適合大表的查詢，可以使用層次查詢來解決。connect by xxx start with xxx

level是層次查詢提供的偽列，需要顯示使用才會查詢這個偽列。

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

子查詢

子查詢語法中的小括號

必須要有小括號，書寫風格要清晰如下圖所示：

範例：查詢比FORD工資高的員工

select * from emp where sal > (select sal from emp where ename='FORD')

可以使用子查詢的位置

select，from，where，having

select位置的子查詢只能是單行子查詢，也就是隻能返回一條結果

select empno,ename,sal,(select job from emp where empno='7839') job from emp

having位置的子查詢

範例：查詢部門平均工資大於30號部門最大工資的部門號及其平均工資

select deptno,avg(sal) from emp group by deptno having avg(sal) > (select max(sal) from emp where deptno=30)

from位置的子查詢

查詢結果也可以當成表

select * from (select empno,ename,sal from emp)

增加1列年薪，使用sal*12得到年薪

select * from (select empno,ename,sal,sal*12 annsal from emp)

主查詢和子查詢可以不是同一張表

範例：查詢部門名稱是SALES的員工資訊

使用子查詢的方式：

select * from emp where deptno=(select deptno from dept where dname='SALES')

使用多表查詢的方式:

select e.* from emp e, dept d where e.deptno=d.deptno and d.dname='SALES'

子查詢的排序

一般不在子查詢中，使用排序；但在Top-N分析問題中，必須對子查詢排序

範例：找到員工表中工資最高的前三名，如下格式：

rownum，行號，oracle自動為表分配的偽列。

• 行號永遠按照預設的順序生成
• 行號只能使用<,<=；不能使用>,>=

select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<=3

子查詢執行順序

一般先執行子查詢，再執行主查詢；單相關子查詢例外。

相關子查詢範例：找到員工表中薪水大於本部門平均薪水的員工

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal > (select avg(sal) from emp where deptno=e.deptno)

單行子查詢和多行子查詢

單行子查詢返回一個結果，只能使用單行操作符；

多行子查詢返回多個結果，只能使用多行操作符。

單行操作符：

多行操作符：

單行子查詢範例1：

查詢員工資訊，要求：

職位與7566員工一樣，薪水大於7782員工的薪水

select * from emp where job=(select job from emp where empno=7566) and sal >（select sal from emp where empno=7782）

單行子查詢範例2：

查詢最低工資大於20號部門最低工資的部門號和部門的最低工資

select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20)

多行子查詢範例：

查詢部門名稱是SALES和ACCOUNTING的員工資訊

使用多行子查詢的方式：

select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING')

使用多表查詢的方式：

select e.* from emp e, dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING')

子查詢中的空值問題

查詢不是老闆的員工

注意：當子查詢中包含null值時，不要使用not in。

a not in (10,20,null)

a != 10 and a != 20 and a != null, a != null 永遠不成立，所以整個表示式永遠返回false。

可以在子查詢中把null值過濾掉再使用not in。

select * from emp where empno not in (select mgr from emp where mgr is not null)