oracle 分組函數

1.

select deptno,row_number() over(partition by deptno order by sal) from emp order by deptno;

2.

select deptno,rank() over (partition by deptno order by sal) from emp order by deptno;

3.

select deptno,dense_rank() over(partition by deptno order by sal) from emp order by deptno;

4.

select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) from emp ord er by deptno;

5.

select deptno,ename,sal,lag(ename,2,'example') over(partition by deptno order by ename) from em p

order by deptno;

6.

  select deptno, sal,sum(sal) over(partition by deptno) from emp;--每行記錄後都有總計值
  select deptno, sum(sal) from emp group by deptno;

7. 求每個部門的平均工資以及每個人與所在部門的工資差額

select deptno,ename,sal ,
       round(avg(sal) over(partition by deptno)) as dept_avg_sal,
       round(sal-avg(sal) over(partition by deptno)) as dept_sal_diff
   from emp;