> 來源：[恆生LIGHT雲社群](https://developer.hs.net/thread/1460 "恆生LIGHT雲社群")

## **Oracle高階查詢**

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

## **分組查詢**

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

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

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

![image-20211015163201911.png](https://developer.hs.net/storage/attachments/2021/10/18/TInbsBku2AGOBnVjwtloUTF7zaMYObh5n9OEJnbN_thumb.png "3384")

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

![image-20211015170729835.png](https://developer.hs.net/storage/attachments/2021/10/18/fuD6E6nnj4htNlxfXmdV706uSI6x4W8vPoHk9fQt_thumb.png "3385")

### **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
```

![image-20211018102217609.png](https://developer.hs.net/storage/attachments/2021/10/18/7LyULRuOrFeBjYJnH6KT95MMP4SgwMVaJqZt4aG3_thumb.png "3390")

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語句報表功能

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

![image-20211018104944429.png](https://developer.hs.net/storage/attachments/2021/10/18/UheQPEc8peDunG5DJRS2bnHnmtCCAHzq37RV6UT5_thumb.png "3396")

可以使用rollup函式

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

![image-20211018105432083.png](https://developer.hs.net/storage/attachments/2021/10/18/aRaMB1Qg1mq4EaUVRP2otX1txN1zL3GsHbHJq5f0_thumb.png "3397")

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

![image-20211018105638601.png](https://developer.hs.net/storage/attachments/2021/10/18/FDdRgg2ZJipeqOKoO93eImaW0CwYblHlsBieohZp_thumb.png "3398")

### **完善報表顯示**

增加標題，頁碼等

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

![image-20211018111443518.png](https://developer.hs.net/storage/attachments/2021/10/18/aPvrLS7IfO0WIAZzHY12ksjBi4UyCD7Pdk3nZyYh_thumb.png "3399")

## **多表查詢**

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

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

![image-20211018112731881.png](https://developer.hs.net/storage/attachments/2021/10/18/O693AqMxyPzIb6uQbsmEk3k722TfXbtnbQwNdVTl_thumb.png "3400")

### **等值連線**

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

需要查詢員工表和部門表，透過部門號進行等值連線查詢，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
```

![image-20211018133100426.png](https://developer.hs.net/storage/attachments/2021/10/18/lWONZE2tip0nLcSKN55khJyTsW6qoBRGvEXNfN8j_thumb.png "3403")

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

![tAq5DdvrtZwXbiOcmRgXRyGjIwEJsFyin9sAwA9V_thumb.png](https://developer.hs.net/storage/attachments/2021/10/18/y5zdQxAkCZ5Q0SUYZy29DQ7bmqAIWH66nhMh48mn_thumb.png "3411")

使用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分析問題中，必須對子查詢排序

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

![image-20211018145110072.png](https://developer.hs.net/storage/attachments/2021/10/18/BKkNGdNYKk2OToPqgcx0UVnV2RYw5YFHbo6ZVRbS_thumb.png "3410")

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)
```

### **單行子查詢和多行子查詢**

單行子查詢返回一個結果，只能使用單行運算子；

多行子查詢返回多個結果，只能使用多行運算子。

#### **單行運算子：**

| 運算子 | 含義     |
| ------ | -------- |
| =      | 等於     |
| >      | 大於     |
| >=     | 大於等於 |
| <      | 小於     |
| <=     | 小於等於 |
| <>     | 不等於   |

#### **多行運算子：**

| 運算子 | 含義                         |
| ------ | ---------------------------- |
| in     | 等於列表中的任何一個         |
| any    | 和子查詢返回的任意一個值比較 |
| all    | 和子查詢返回的左右值比較     |

#### **單行子查詢示例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)
```

「Oracle」Oracle高階查詢介紹

相關文章