oracle分析函数的使用

创建表和表数据：

create table emp(
deptno varchar2(20),--部门编码
ename varchar2(20),--人名
sal number(10));--工资

insert into emp values('10','andy1',2000);
insert into emp values('10','andy2',3000);
insert into emp values('10','andy3',2000);
insert into emp values('20','leno1',4000);
insert into emp values('20','leno2',8000);
insert into emp values('20','leno3',6000);
insert into emp values('30','jack1',5000);
insert into emp values('30','jack2',6000);
insert into emp values('30','jack3',7000);

下面sql根据不同的条件查询不同的数据

select * from emp;
--查询部门的平均薪水
select deptno,ename,sal,avg(sal)over(partition by deptno) from emp;
--查询部门的总薪水
select deptno,ename,sal,sum(sal)over(partition by deptno) from emp;
--查询部门的人数
select deptno,ename,sal,count(ename)over(partition by deptno) from emp;
--每个部门的最高薪水
select deptno,ename ,sal,first_value(sal)over(partition by deptno order by sal desc) from emp;
select deptno,ename ,sal,max(sal)over(partition by deptno order by sal desc) from emp;
--每个部门的最低薪水
select deptno,ename ,sal,first_value(sal)over(partition by deptno order by sal) from emp;
select deptno,ename ,sal,min(sal)over(partition by deptno order by sal) from emp;
--求总和,每人占总额的百分比
select deptno,ename,sal,sum(sal)over(),round(sal/sum(sal)over(),4)*100||'%' from emp;

 --连续求和
 select deptno,ename,sal,sum(sal)over(order by ename) from emp;

 --不连续求和
select deptno,ename,sal,sum(sal)over() from emp;

--按部门薪水连续连续的求和
select deptno,ename,sal,sum(sal)over(partition by deptno order by ename) from emp;
 
--按部门求总和
select deptno,ename,sal,sum(sal)over(partition by deptno) from emp;

--不按部门连续求总和
select deptno,ename,sal,sum(sal)over(order by deptno,ename desc) from emp;

--查询各部门中工资最高的记录
select * from (
select deptno,ename,sal,row_number()over(partition by deptno order by sal desc) top from emp
) where top = 1;

--按薪水高低对每个员工在本部门和整个公司内的排名进行排序
select deptno,ename,sal,dense_rank()over(partition by deptno order by sal desc) as dept_ranking,dense_rank()over(order by sal desc) as company_rank from emp
order by sal desc;

注意：
1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果
2.rank()和dense_rank()的区别是：
rank()是跳跃排序，有两个第二名时接下来就是第四名
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

 文件参考：http://blog.csdn.net/zhou689689/article/details/41512267