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