The views and opinions expressed all for my own,only for study and test, not reflect the views of Any Company and its affiliates.
分类： Oracle
2008-10-23 18:06:59
select RANK() over (order by sal desc),rank() over (partition by deptno order by sal desc),a.empno,a.deptno,a.sal,lag(sal,1) over (partition by deptno order by sal desc) - sal from emp a order by deptno,sal desc; RANK()OVER(ORDERBYSALDESC) RANK()OVER(PARTITIONBYDEPTNOORDERBYSALDESC) EMPNO DEPTNO SAL LAG(SAL,1)OVER(PARTITIONBYDEPTNOORDERBYSALDESC)-SAL-------------------------- ------------------------------------------- ---------- ---------- ---------- --------------------------------------------------- 2 1 7839 10 5000 7 2 7782 10 2450 2550 9 3 7934 10 1300 1150 3 1 7902 20 3000 3 1 7788 20 3000 0 5 3 7566 20 2975 25 12 4 7876 20 1100 1875 15 5 7369 20 800 300 1 1 7890 30 5600 6 2 7698 30 2850 2750 8 3 7844 30 1500 1350 10 4 7521 30 1250 250 10 4 7654 30 1250 0 12 6 7499 30 1100 150 14 7 7900 30 950 150