Oracle（五）聚合函数
**************=======Oracle中的聚合函数===========**************
-- 聚合函数
--查询总记录数 count(1) 替换count(*)
select count(1) from teacher
--查询薪水总和
select sum(sal) from teacher
--查询最高薪水
select max(sal) from teacher
--查询最低薪水
select min(sal) from teacher
--查询平均薪水
select avg(sal) from teacher
--查询各个部门的 编号，薪水（平均，最高，总和）
-- 按照部门的总薪水进行 降序排列
select deptno,avg(sal),max(sal),sum(sal)
from teacher
group by deptno
order by sum(sal) desc
-- 再增加一个条件 （部门人数在10人以上）
select deptno,avg(sal),max(sal),sum(sal)
from teacher
group by deptno
having count(deptno)>10
order by sum(sal) desc
--验证我们的 部门编号 为空的
select count(1) from teacher where deptno is null
--分析函数
--01.rank()：具有相等值的 排位相同，但是之后的排名跳跃
select sal,tname from teacher order by sal
select sal,tname,
rank() over(order by sal) 薪水排名
from teacher
--02.dense_rank()：具有相等值的 排位相同，之后的排名连续
select sal,tname,
dense_rank() over(order by sal) 薪水排名
from teacher
--03.row_number():不管值是否相等，排名都是连续的
select sal,tname,
row_number() over(order by sal) 薪水排名
from teacher
-- 查询各个部门（按照部门查询）的教师薪水 降序后的结果
-- partition by deptno 按照部门进行分组
select deptno,tname,sal,
rank() over(partition by deptno order by sal desc) 薪水排名
from teacher
-- rank()/dense rank()/ row_number() over(partition by 分组的字段 order by 排序的字段 desc（降序）/asc(默认升序))
*************==========联合查询============****************
-- 联合查询
--01.union 并集（两个结果集中所有的数据） 重复数据显示一次
select tno from teacher where tno<1020
union
select tno from teacher where tno<1025
--02.union all并集（两个结果集中所有的数据） 重复数据显示多次
select tno from teacher where tno<1020
union all
select tno from teacher where tno<1025
--03.intersect 交集（取两个结果集中公共的数据）
select tno from teacher where tno<1020
intersect
select tno from teacher where tno<1025
--04.补集 minus a>b 取a-b
select tno from teacher where tno<1025
minus
select tno from teacher where tno<1020
--伪列：没有存储在真正的表中，但是，可以在表中查询，不能进行增删改操作！
-- rowid:表中行的存储地址！ A-Za-z0-9+/ 第二位变成B 依次类推
select rowid from teacher
select rowid,tname from teacher where rowid='AAASRxAAGAAAAJ7AAA'
--rownum：查询返回结果集中 行的编号！ 分页的时候使用
--rownum只能对=1或者<n进行操作！
--对于>n这种结果怎么查询？
--01.建立临时表
--02.让伪列rownum成为临时表中真正的列
--03.使用伪列的别名进行操作
--查询教师表中 薪水最高的前5名
select tname,rownum from teacher order by sal desc
--上面的sql执行后 有rownum 是乱序的
--那么我们就重新分配rownum
--怎么重新分配？ 再次查询 查询的表就是上面的sql
select tname,rownum from
(select * from teacher order by sal desc)
where rownum<6
--执行上面的sql后 发现 rownum 有序了 而且是薪水的降序排列
select * from
(select * from teacher order by sal desc)
where rownum<6 -- 得到薪水的前5名
--查询教师表中第5名的
select * from
(select * from teacher order by sal desc)
where rownum=5 -- 不可行
select * from
(select t.*,
dense_rank() over(order by sal desc) ranks from teacher t)
where ranks=5
-- 查询6-10条信息
--01.使用分析函数
select * from
(select t.*,
dense_rank() over(order by sal desc) ranks from teacher t)
where ranks>5 and ranks<11
--02.三层嵌套
select * from teacher order by sal desc
-- rownum是乱序 但是薪水已经降序排列了
select t.*,rownum rw from
(select * from teacher order by sal desc) t
-- 我们把上面的sql 当成一个临时表
-- rownum 进行了重新排序！
--接下来 就开始查询
select * from
(select t.*,rownum rw from
(select * from teacher order by sal desc) t)
where rw>5 and rw<11
