分析函数

--row_number

SELECT ename,

deptno,

rownum,

row_number() over(ORDER BY deptno) rn,

rank() over(ORDER BY deptno) rk, --跳号

dense_rank() over(ORDER BY deptno) dense_rk --不跳号

FROM emp-- over 必须跟order by

--row_number

SELECT ename,

deptno,

rownum,

row_number() over(partition by deptno ORDER BY deptno) rn,

rank() over(partition by deptno ORDER BY deptno) rk,

dense_rank() over(partition by deptno ORDER BY deptno) dense_rk

FROM emp

--row_number

SELECT ename,

deptno,

rownum,

row_number() over(partition by deptno ORDER BY sal) rn,

rank() over(partition by deptno ORDER BY sal) rk,

dense_rank() over(partition by deptno ORDER BY sal) dense_rk

FROM emp

--求每个部门的最高工资

SELECT *

FROM (SELECT ename,

deptno,

rownum,

row_number() over(PARTITION BY deptno ORDER BY sal DESC) rn

--rank() over(partition by deptno ORDER BY sal) rk,

--dense_rank() over(partition by deptno ORDER BY sal) dense_rk

FROM emp)

WHERE rn = 1

--sum,avg,max,min

SELECT ename,

deptno,

sal,

SUM(sal) over(PARTITION BY deptno) AS "部门汇总"， SUM(sal) over() AS "全部汇总"

FROM emp;

--累加

SELECT ename,

deptno,

sal,

SUM(sal) over(order BY sal)， SUM(sal) over(order BY sal,rowid)

FROM emp;

SELECT ename,

deptno,

sal,

avg(sal) over(PARTITION BY deptno) AS "部门汇总"， avg(sal) over() AS "全部汇总"

FROM emp;

SELECT empno,

ename,

sal

FROM emp a

WHERE sal = (SELECT MIN(sal) FROM emp b WHERE a.deptno = b.deptno)

--改写

SELECT *

FROM (

SELECT a.*,

row_number() over(PARTITION BY a.deptno ORDER BY a.sal) rn

FROM emp a)

WHERE rn = 1

SELECT *

FROM emp

WHERE sal in（SELECT MIN(sal) over(PARTITION BY deptno ORDER BY sal) FROM emp a);