分析函数

--row_number

创新互联公司于2013年创立，先为金塔等服务建站，金塔等地企业，进行企业商务咨询服务。为金塔企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

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

文章名称：分析函数
标题URL：http://shouzuofang.com/article/jhdcsd.html