Oracle 数据库基于SCOTT模式的分组技术和子查询

1.在SELECT 语句中，可以使用GROUP BY子句进行分组操作，并且可以使用HAVING子句提供分组条件

---->下面的查询将对SCOTT模式中的EMP表进行分组，以统计各部门的员工人数

 select deptno,count(*) as 员工数量 from emp group by deptno;

------>HAVING子句通常与GROUP BY子句一起使用，以便在完成分组后可以使用HAVING子句对分组的结果进行进一步的筛选。如果不适用GROUP BY子句，HAVING 子句的功能与WHERE 子句一样，对整个表进行筛选。（下面的查询将搜索出所有员工数量大于等于5的部门）

select deptno,count(*) as 员工数量 from emp group by deptno having count(*)
>=5;

----->理解HAVING子句的方法，就是记住SELECT 语句中的各子句的执行次序。在SELECT语句中，首先执行FROM子句找到表，而WHERE子句则在FROM子句输出的数据中进行筛选，HAVING子句则在GROUP BY，WHERE或FROM子句执行后对结果进行筛选。（下面我们使用两个示例分别展示HAVING和WHERE子句对SELECT语句的影响。第一个检索语句使用HAVING子句对分组的结果进行筛选；第二个检索语句则使用WHERE子句对表中的数据进行筛选后再分组）

 select job,avg(sal) as 平均值,max(sal) as 最大值,count(*) as 职工人数 from
emp group by job having avg(sal)>1000;

SQL> select job,count(*) as 职工人数 from
emp where sal>1000 group by job;

----->从查询结果可以看出，WHERE子句直接从表中筛选数据过滤掉了部分员工信息；而HAVING子句则在分组后进行筛选。

2.子查询

2.1 单行子查询

在单行子查询中，该内查询只返回单行单列值，因此可以把这种子查询作为一个常量。在WHERE子句中，可以使用单行比较运算符来比较某个表达式与子查询的结果。可以使用的单行比较运算符包括：等于"=",大于">",大于或等于">=",小于"<",小于或等于"<=",和不等于"<>/!="（如下例子，在子查询中使用统计函数，从EMP表中得到工资最低和工资最高的员工信息）

SQL> select empno,ename,job,sal from emp where sal>=(select max(sal) from emp)
or sal<=(select min(sal) from emp);

------>红线表示工资最低，绿线表示工资最高的员工

------>在执行子查询的过程中，如果内查询的结果是空值，那么外查询的条件始终不会满足，该查询的最终结果也是空值。

SQL> select empno,sal from emp where deptno=(select deptno from dept
where dname='MANAGER');

2.2 多行子查询

多行子查询可以返回单列多行数据。在这种多行子查询中，必须使用多行运算符来判断，而不能使用单行运算符。使用多行运算符可以执行与一个或多个数据的比较操作。在Oracle系统中，可以使用的多行比较运算符包括：IN（等于列表中的任何一值），ANY(与子查询返回的任何一值进行比较) 和ALL（与子查询返回的所有值进行比较）。

ANY运算符表示与子查询中的任何一个值进行比较。这时，需要将单行比较运算符与该运算符组合起来使用。

----->例：在子查询的比较条件中，使用>any运算符查询大于MANAGER职位中最小薪金的员工信息。

SQL> select empno,sal from emp where sal>any(select sal from emp where
 job='MANAGER');

----->使用IN运算符的多行子查询，用于查询属于ACCOUNTING和RESEARCH部门的员工信息

SQL> select empno,sal,deptno from emp where deptno in (select deptno
from dept where dname='ACCOUNTING' or dname='RESEARCH');

----->使用ALL运算符的多行子查询，用于查询薪金大于所有MANAGER职位的员工信息

SQL> select empno,sal from emp where sal>all(select sal from emp where
 job='MANAGER');

2.3 关联子查询

在前面两种子查询中，内查询和外查询是分开执行的，即内查询的执行和外查询的执行是没有关系的，外查询仅仅是使用了内查询的最终结果。

但如果在子查询语句中，内查询的执行需要借助于外查询，而外查询的执行又离不开内查询的执行。这时内查询和外查询是相互关联的，这种子查询称为关联子查询。

需要注意的是，关联子查询是对同一个表进行查询操作，所以内层子查询返回的记录都是外层查询所操作的候选对象，所以当数据量较大时，会导致查询效率低下。

------>现在使用关联子查询检索某个职位的员工薪金是否超出了平均水平

SQL> select ename,sal from emp t where sal> (select avg(sal) from emp where
t.job=job) order by job;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!