1. over partition by 和 group by的区别
- over partition by 可以将汇总数据和源数据在一行中显示
例如: 查询每个员工的薪资和部门最高薪资
- group by 只能显示分组数据和聚集结果
2. rank()/dense_rank() over(partition by ...order by ...)
--查询每个部门工资最高的雇员的信息
select * from (select emp.*,rank() over(partition by deptno order by sal desc) maxsalno from emp) where maxsalno=1
3. min()/max() over(partition by ...)
--查询每位雇员信息的同时算出雇员工资与所属部门最高/最低员工工资的差额
select a.*,sal-maxsal diff from (select emp.*,max(sal) over(partition by deptno) maxsal from emp) aEMPNO ENAME DEPTNO SAL DIFF
---------- -------------------- ---------- ---------- ----------
7782 CLARK 10 2450 -2550
7839 KING 10 5000 0
7934 MILLER 10 1300 -3700
7566 JONES 20 2975 -25
7902 FORD 20 3000 0
7876 ADAMS 20 1100 -1900
7369 SMITH 20 800 -2200
7788 SCOTT 20 3000 0
7521 WARD 30 1250 -1600
7844 TURNER 30 1500 -1350
7499 ALLEN 30 1600 -1250
EMPNO ENAME DEPTNO SAL DIFF
---------- -------------------- ---------- ---------- ----------
7900 JAMES 30 950 -1900
7698 BLAKE 30 2850 0
7654 MARTIN 30 1250 -1600
14 rows selected.
select a.empno,a.ename,a.deptno,a.sal,maxsal,sal-maxsal diff from (select emp.*,max(sal) over(partition by deptno order by sal) maxsal from emp) a
EMPNO ENAME DEPTNO SAL MAXSAL DIFF
---------- -------------------- ---------- ---------- ---------- ----------
7934 MILLER 10 1300 1300 0
7782 CLARK 10 2450 2450 0
7839 KING 10 5000 5000 0
7369 SMITH 20 800 800 0
7876 ADAMS 20 1100 1100 0
7566 JONES 20 2975 2975 0
7788 SCOTT 20 3000 3000 0
7902 FORD 20 3000 3000 0
7900 JAMES 30 950 950 0
7654 MARTIN 30 1250 1250 0
7521 WARD 30 1250 1250 0
EMPNO ENAME DEPTNO SAL MAXSAL DIFF
---------- -------------------- ---------- ---------- ---------- ----------
7844 TURNER 30 1500 1500 0
7499 ALLEN 30 1600 1600 0
7698 BLAKE 30 2850 2850 0
14 rows selected.
*order by的作用
表示当前行中所在分组中相同排序序号的(max最大值)
select a.empno,a.ename,a.deptno,a.sal,sum(sal) over(partition by deptno order by sal) maxsal from emp a where deptno=20;
EMPNO ENAME DEPTNO SAL MAXSAL
---------- -------------------- ---------- ---------- ----------
7369 SMITH 20 800 800
7876 ADAMS 20 1100 1900
7566 JONES 20 2975 4875
7902 FORD 20 3000 10875 顺序号相同4 3000+3000+4875=10875
7788 SCOTT 20 3000 10875 顺序号相同4
* 有排序，当前行在分组内相同顺序号的行 进行求和
(4).lead()/lag() over(partition by ... order by ...)
-- 计算个人工资与比自己高一位/低一位工资的差额
select EMP.*,sal-lead(SAL,1,sal) over(partition by deptno order by sal) after,sal-lag(sal,1,sal) over(partition by deptno order by sal) before from EMP;select a.empno,a.ename,a.deptno,a.sal,sal-lead(SAL,1,sal) over(partition by deptno order by sal) after,sal-lag(sal,1,sal) over(partition by deptno order by sal) before from EMP;
EMPNO ENAME DEPTNO SAL AFTER BEFORE
---------- -------------------- ---------- ---------- ---------- ----------
7934 MILLER 10 1300 -1150 0
7782 CLARK 10 2450 -2550 1150
7839 KING 10 5000 0 2550
7369 SMITH 20 800 -300 0
7876 ADAMS 20 1100 -1875 300
7566 JONES 20 2975 -25 1875
7788 SCOTT 20 3000 0 25
7902 FORD 20 3000 0 0
7900 JAMES 30 950 -300 0
7654 MARTIN 30 1250 0 300
7521 WARD 30 1250 -250 0
EMPNO ENAME DEPTNO SAL AFTER BEFORE
---------- -------------------- ---------- ---------- ---------- ----------
7844 TURNER 30 1500 -100 250
7499 ALLEN 30 1600 -1250 100
7698 BLAKE 30 2850 0 1250
14 rows selected.
* lead(a,b,c) 表示当前行列a的下b行的值，如果没有则去值为c
lag(a,b,c) 表示当前行列a的上b行的值，如果没有则去值为c
