sql子查询的一些例子

### SQL子查询实例详解 #### 一、基本概念与应用场景 在SQL中，子查询（也称为嵌套查询）是指在一个查询语句中包含另一个查询语句的情况。它是一种非常强大的功能，可以用来解决复杂的查询需求，特别是在处理多表关联、筛选特定条件等方面有着广泛的应用。子查询可以放在SELECT、FROM、WHERE等子句中，根据其位置不同，子查询的执行顺序也会有所不同。 #### 二、示例分析 ##### 1. 查询在“NEWYORK”部门工作的员工信息 **SQL语句**： ```sql SELECT ename, deptno, sal FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc = 'NEWYORK') ``` **知识点解析**： - **子查询**: `(SELECT deptno FROM dept WHERE loc = 'NEWYORK')` 是一个子查询，它的作用是从`dept`表中找到所有位于“NEWYORK”的部门编号。 - **主查询**: 主查询通过`WHERE deptno = (子查询结果)`来筛选出在这些部门工作的员工。 - **应用场景**: 当需要根据另一个表中的数据来过滤当前表的数据时，这种类型的子查询非常有用。 ##### 2. 查询在部门名称以“A”开头的部门工作的员工信息 **SQL语句**： ```sql SELECT ename, job, sal FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname LIKE 'A%') ``` **知识点解析**： - **子查询**：`(SELECT deptno FROM dept WHERE dname LIKE 'A%')` 子查询用于找出所有部门名称以“A”开头的部门编号。 - **IN关键字**：主查询中的`WHERE deptno IN (子查询结果)`表示选择那些部门编号在子查询结果集中的员工记录。 - **LIKE操作符**：子查询中的`dname LIKE 'A%'`表示查找所有以“A”开头的部门名称。 ##### 3. 查询每个部门薪资最高的员工信息 **SQL语句**： ```sql SELECT deptno, ename, job, sal FROM emp WHERE (deptno, sal) IN (SELECT deptno, MAX(sal) FROM emp GROUP BY deptno) ``` **知识点解析**： - **子查询**：`(SELECT deptno, MAX(sal) FROM emp GROUP BY deptno)` 用于找出每个部门的最高薪资。 - **主查询**：通过比较`WHERE (deptno, sal) IN (子查询结果)`来筛选出每个部门薪资最高的员工。 - **GROUP BY子句**：子查询中的`GROUP BY deptno`用于按部门分组，并计算每个部门的最高薪资。 ##### 4. 使用ROWNUM函数进行数据分页 **SQL语句**： ```sql (1) SELECT ename, job, sal, rownum FROM (SELECT ename, job, sal FROM emp ORDER BY sal) (2) SELECT ename, job, sal, rownum FROM (SELECT ename, job, sal FROM emp ORDER BY sal) WHERE rownum <= 5 ``` **知识点解析**： - **ROWNUM函数**：Oracle数据库特有的函数，用于为结果集中的每一行分配一个唯一的行号。 - **子查询**：`(SELECT ename, job, sal FROM emp ORDER BY sal)` 用于获取排序后的员工列表。 - **数据分页**：`(2)`中的查询使用`WHERE rownum <= 5`来实现数据分页，即只显示前5条记录。 ##### 5. 使用HAVING子句结合子查询进行聚合筛选 **SQL语句**： ```sql SELECT deptno, job, AVG(sal) FROM emp GROUP BY deptno, job HAVING AVG(sal) > (SELECT sal FROM emp WHERE ename = 'MARTIN') ``` **知识点解析**： - **子查询**：`(SELECT sal FROM emp WHERE ename = 'MARTIN')` 用于找出名为“MARTIN”的员工薪资。 - **聚合函数**：`AVG(sal)` 用于计算平均薪资。 - **HAVING子句**：`HAVING AVG(sal) > (子查询结果)` 用于筛选出平均薪资高于“MARTIN”员工薪资的部门和职位组合。 #### 三、总结 通过以上几个示例，我们可以看到子查询在SQL中的应用非常广泛，它可以极大地提高查询的灵活性和效率。无论是简单的数据筛选还是复杂的多表关联和聚合操作，子查询都能够提供一种简洁且高效的方式来实现。在实际工作中，熟练掌握并灵活运用子查询是非常重要的。