--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
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() 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
--求每個部門的最高工資
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;
--累加
SUM(sal) over(order BY sal), SUM(sal) over(order BY sal,rowid)
avg(sal) over(PARTITION BY deptno) AS "部門彙總", avg(sal) over() AS "全部彙總"
SELECT empno,
ename,
sal
FROM emp a
WHERE sal = (SELECT MIN(sal) FROM emp b WHERE a.deptno = b.deptno)
--改寫
FROM (
SELECT a.*,
row_number() over(PARTITION BY a.deptno ORDER BY a.sal) rn
FROM emp a)