语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
简单的说row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序,再为降序以后的没条xlh记录返回一个序号。
示例:
xlh row_num
1700 1
1500 2
1085 3
710 4
row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)
实例:Oracel,scott-emp表中,怎么查出每个部门工资前三名的员工信息
select tt.empno,tt.ename,b.dname,tt.sal,tt.rn from
(select t.empno,t.ename,t.deptno,t.sal,row_number()over(partition by deptno order by sal desc) rn from emp t)tt,dept b
where b.deptno=tt.deptno and tt.rn<=3;
--------------------------------------
select * from ( SELECT a.deptno,(rownum+1-b.min_sno) sno, a.ename,a.sal FROM (SELECT * FROM emp ORDER BY deptno, sal ) a, (SELECT deptno, MIN(rownum) min_sno FROM( SELECT * FROM emp ORDER BY deptno, sal desc) GROUP BY deptno) b WHERE a.deptno=b.deptno ) where sno <=3
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25380220/viewspace-717153/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25380220/viewspace-717153/