> 来源：[恒生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)
```

ITPub博客

「Oracle」Oracle高级查询介绍

最新文章