row_number() over ([partition by col1] order by col2) ) as 别名 表示根据col1分组,在分组内部根据 col2排序 而这个“别名”的值就表示每组内部排序后的顺序编号(组内连续的唯一的),[partition by col1] 可省略。 以Scott/tiger登陆,以emp表为例。 1、select deptno,ename,sal, sum(sal) over (order by ename) 累计, --按姓名排序,并将薪水逐个累加 sum(sal) over () 总和 , -- 此处sum(sal) over () 等同于sum(sal),求薪水总和 100*round(sal/sum(sal) over (),4) "份额(%)" --求每个人的薪水占总额的比例,小数点后保留2位,括号和百分号为特殊符号,所以需要“” from emp 结果如下 : 2、select deptno,ename,sal, sum(sal) over (partition by deptno order by ename) 部门连续求和,--partition by deptno先按部门分组,再按姓名排序,并将薪水逐个累加 sum(sal) over (partition by deptno) 部门总和, -- 每个部门的薪水总和 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",--每个员工在各自部门的薪水比例 sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal),所有员工的薪水总和 100*round(sal/sum(sal) over (),4) "总份额(%)" --求每个人的薪水占总额的比例 from emp 结果如下: 3、select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,--根据部门分组,再按部门内的个人薪水排序,逐个累加。 sum(sal) over (order by deptno,sal) sum --按部门排序,将薪水逐个累加。 from emp; 结果如下: 4、部门从大到小排列,部门里各员工的薪水从高到低排列 select deptno,ename,sal, sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,--按部门分组后,按部门和薪水降序排 sum(sal) over (order by deptno desc,sal desc) sum --按部门和薪水降序排 from emp; 结果如下:
5、将各部门的员工按薪水排序 select ename,job,deptno,sal,(row_number() over(partition by deptno order by sal desc)) as 排名 --先按部门分组,再在部门中按薪水降序排名 from scott.emp 结果如下: 6、查找各部门中薪水最高的前2位 select ename,job,deptno,sal,排名 from (select ename,job,deptno,sal,(row_number() over(partition by deptno order by sal desc)) as 排名 from scott.emp --先将各部门的员工按薪水排序,再在结果中取出需要的部分 ) where 排名<=2; 结果如下: 7、如果已经在over()中进行过分组,在"... from emp;"后面不要加order by 子句。 转自http://blog.163.com/huiping_z/blog/static/8054131220091195378488/ |
转发:oracle分析函数row_number() over()使用
最新推荐文章于 2021-08-16 14:58:33 发布
【转】oracle分析函数row_number() over()使用
2010-07-09 21:14