--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);
