[20170525]分析函数first_value.txt
--//昨天看sql语句,发现居然分析函数first_value.我一直认为开发如果要做一些报表需要了解学习一些oracle分析函数的知识,我发现许
--//多开发这方面一篇空白.我曾经更开发讲过,为什么自己不会一点点看书,仅仅1,2天有个大概记忆,以后在baidu,google查询就ok了.
--//可惜遇到这样的问题还是写出一些变态的sql语句,无语!!
--//通过例子说明:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> select emp.*,first_value(sal) over (partition by deptno order by sal desc) deptmax_sal from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTMAX_SAL
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -----------
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 5000
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 5000
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 5000
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 3000
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 3000
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 3000
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 3000
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 3000
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 2850
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 2850
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 2850
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 2850
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 2850
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 2850
14 rows selected.
--//这样可以查询部门最高工资.我以前一般这样写.
SCOTT@book> select emp.*,max(sal) over (partition by deptno order by sal desc) deptmax_sal from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTMAX_SAL
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -----------
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 5000
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 5000
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 5000
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 3000
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 3000
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 3000
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 3000
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 3000
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 2850
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 2850
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 2850
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 2850
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 2850
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 2850
14 rows selected.
--//使用first_value分析函数的好处可以显示别的字段,不一定是sal.
--//比如要查询显示每个部门工资最高的记录.
SCOTT@book> select * from (select emp.*,first_value(empno) over (partition by deptno order by sal desc) maxsal_empno from emp ) where empno=MAXSAL_EMPNO;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MAXSAL_EMPNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ------------
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7839
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7788
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7698
--//我以前的写法存在一些问题,如下:
SCOTT@book> select * from (select emp.*,max(sal) over (partition by deptno order by sal desc) maxsal from emp ) where sal=MAXSAL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MAXSAL
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 5000
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 3000
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 3000
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 2850
--//这样查询部门deptno=20有2条记录.当然还有许多写法,总之开发应该抽一点点时间学习一些分析函数知识.
