oracle 分组查询 子查询

分组查询

例：

统计平均工资大于2000的部门信息

-- 1.确定要查询的字段及表

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

-- 2.统计平均工资及部门信息

select avg(e.sal),d.deptno, d.dname, d.loc from emp e,dept d

where e.deptno= d.deptno group by d.deptno, d.dname, d.loc

-- 3.统计平均工资大于2000 的部门信息

select trunc(avg(e.sal)) avg,count(*) total,d.deptno, d.dname, d.loc from emp e,dept d

where e.deptno= d.deptno group by d.deptno, d.dname, d.loc

HAVING avg(e.sal)>2000

注意：where 和 having的区别

where: 是在执行group by 操作之前进行的过滤，表示从全部数据之中筛选出部分的数据

在where之中不能使用统计函数

having:是在group by 分组之后的再次过滤，可以在having子句中使用统计函数

查询语句顺序与电脑执行顺序：

综合练习 例：

--显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，

--并且要满足从事同一工作的雇员的月工资合计大于$5000，

--输出结果按月工资的合计升序排列：

select job,sum(sal) from emp where job<>'SALESMAN' group by job

having sum(sal)>5000

order by sum(sal)

子查询：

子查询=简单查询+限定查询+多表查询+统计查询的综合体

所谓的子查询指的就是在一个查询之中嵌套了其他的若干查询，嵌套子查询之后的查询SQL语句如下：

如果子查询返回的是多行单列数据的话，需要使用三种判断符: in、 any、 all

in操作符：用于指定一个子查询的判断范围

例:

select * from emp where sal in(select sal from emp where job='MANAGER')

any操作符：与每一个内容相匹配，有三种匹配形式

例:

-- 等于any 与in作用一样

select * from emp where sal=any(select sal from emp where job='MANAGER')

-- 大于any 比子查询中返回的最小结果要大

select * from emp where sal>any(select sal from emp where job='MANAGER')

-- 小于any 比子查询中返回的最大结果要小

select * from emp where sal<any(select sal from emp where job='MANAGER')

all操作符: 与每一个内容相匹配，有两种匹配形式

例：

-- 大于all 比子查询中返回的最大结果还要大

select * from emp where sal>all(select sal from emp where job='MANAGER')

-- 小于all 比子查询中返回的最小结果还要小

select * from emp where sal<all(select sal from emp where job='MANAGER')

from中的子查询

在from子句中出现的查询，这个子查询一般返回的是多行多列的数据，

当作一张临时表的方式来处理查询出每个部门的编号、名称、位置、部门人数、

平均工资通过子查询完成，所有的统计查询只能在group by中出现，所以在子

查询之中负责统计数据，而在外部的查询中，负责将统计数据和dept表数据相

统一

例：

子查询总结

大部分情况下，如果最终的查询结果之中需要出现select子句，但是又不能直接使用统计函数的时候

就在子查询中统计信息，即：有负责统计的地方大部分都需要子查询