分析函數

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