Oracle子查询举例

--组函数，将多行数据输入函数，得到一行结果
--avg求平均值，产生小数部分
select avg(s_age)
from student;
--关于空值,避开空值，只统计有具体值的行。
select avg(comm) from emp;

--count计数器，统计有多少行
--统计有多少个员工
select count(ename) from emp;
--关于空值,避开空值，只统计有具体值的行。
select count(comm) from emp;
--count(*)用于统计所有行
select count(*) from emp;
--max最大值
select max(sal) from emp;
--min最小值
select min(sal) from emp;
--sum求和
select sum(comm) from emp;
--使用distinct 统计不重复值
select count(distinct deptno) from emp;

--分组统计，使用（group by 字段名） 子句，按照某个字段的值分组
--空值也会分一组
--group by 字段名1,字段名2...多层次分组
--只用在group by子句出现过的字段，才能在select中出现，不然只能是组函数
--统计每一个部门有多少人
select deptno,count(*) from emp group by deptno;
--统计每一个部门从事每一种工作的有多少人
select deptno,job,count(*) from emp group by deptno,job;
--练习，查询每个班的平均成绩，列出班级名称、平均成绩
select st.s_class,avg(sc.sco)
from student st,score sc
where st.s_id=sc.s_id
group by st.s_class;

--查询每一个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
--查询平均工资在2000以上的部门，平均工资
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>2000;
--对组函数的过滤要写在having子句中，having子句放在group by后面，order by 前面
--where子句先执行，语法上放在group by前面
select deptno,avg(sal)
from emp
where sal>1000
group by deptno
having avg(sal)>2000;

--组函数的嵌套,先查出平均工资，再获取最大值
select max(avg(sal))
from emp
group by deptno;

--over的使用，跟统计函数一起，构成
--不加参数，会将多行结果和组函数结果分别匹配
select ename,sum(sal) over() from emp;
--加参数order by，会执行排序，然后进行累加
select ename,sal,sum(sal) over(order by ename) from emp;
--partition by可以产生分组的效果
select ename,sal,deptno,sum(sal) over(partition by deptno order by ename) from emp;

--根据工资进行排名
--像行号，不考虑重复值
select ename,sal,row_number() over(order by sal) from emp;
--重复值排名一样，下一个排名是实际行数开始
select ename,sal,rank() over(order by sal) from emp;
--重复值排名一样，下一个排名紧跟上一个排名
select ename,sal,dense_rank() over(order by sal) from emp;

--子查询方式：查询谁的工资比ALLEN高
--先查出ALLEN的工资是多少
--再找出工资比这个数字大的
--更符合人的思考过程，但是效率比较低
--思路：将复杂问题拆成几个简单问题，依次实现
select ename,sal from emp
where sal>(select sal from emp where ename='ALLEN');

--哪些人所在部门的平均工资大于2000
--找出哪些部门的平均工资大于2000
select deptno
from emp
group by deptno
having avg(sal)>2000;
--找出指定部门有哪些人
SELECT ename, deptno
  FROM emp
 WHERE deptno IN
       (SELECT deptno FROM emp GROUP BY deptno HAVING AVG(sal) > 2000);
--哪些人所在部门的平均工资大于2000,列出人名，部门，该部门的平均工资
--将子查询的结果当做新的表来看，将新表和员工表做连接查询
select ename,e.deptno,avgsal
from emp e,(SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno HAVING AVG(sal) > 2000) e2 
where e.deptno=e2.deptno;

--in any all
--in 子查询结果中符合其中一个就可以满足条件
--查询哪些员工从事了和10号部门员工同样的工作
select ename,job from emp where job in
(select job from emp where deptno=10);

--any 将子查询结果中的任意一个进行对比，有一个符合，就成立,像or
--找出比10号部门员工工资高的人
select ename,sal from emp 
where sal>any
(select sal from emp where deptno=10);

select ename,sal from emp 
where sal>
(select min(sal) from emp where deptno=10);

--all 将子查询结果中的任意一个进行对比，所有符合，就成立,像and
--找出比10号部门所有员工工资高的人
select ename,sal from emp 
where sal>all
(select sal from emp where deptno=20);

select ename,sal from emp 
where sal>
(select max(sal) from emp where deptno=20);