[20170525]分析函式first_value.txt
[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條記錄.當然還有許多寫法,總之開發應該抽一點點時間學習一些分析函式知識.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2139838/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle分析函式七——分析函式案例Oracle函式
- Oracle聚合函式/分析函式Oracle函式
- 分析函式函式
- 【分析函式】Oracle分析函式之LAG和LEAD函式Oracle
- 分析函式概述函式
- 分析函式 over函式
- Oracle 分析函式Oracle函式
- 分析函式 - LAG函式
- Oracle分析函式Oracle函式
- Oracle分析函式與視窗函式Oracle函式
- 【Analytic】分析函式之MIN函式函式
- 【Analytic】分析函式之MAX函式函式
- 【Analytic】分析函式之AVG函式函式
- 【Analytic】分析函式之RANK函式函式
- 【Analytic】分析函式之COUNT函式函式
- oracle 10g函式大全--分析函式Oracle 10g函式
- sql優化用group by 函式代替分析函式SQL優化函式
- Hive之分析函式Hive函式
- 分析函式——NTILE(n)函式
- 分析函式——統計函式
- oracle 分析函式(轉)Oracle函式
- oracle分析函式(一)Oracle函式
- Oracle常用分析函式Oracle函式
- Oracle分析函式{轉}Oracle函式
- 多維分析函式函式
- Oracle分析函式-6Oracle函式
- Oracle分析函式-5Oracle函式
- Oracle分析函式-4Oracle函式
- Oracle分析函式-2Oracle函式
- Oracle分析函式-1Oracle函式
- Oracle分析函式-3Oracle函式
- oracle的分析函式over 及開窗函式Oracle函式
- 【Analytic】分析函式之ROW_NUMBER函式函式
- 【Analytic】分析函式之DENSE_RANK函式函式
- 【Analytic】分析函式之FIRST_VALUE函式函式
- 【Analytic】分析函式之LAST_VALUE函式函式AST
- mysql 自定義分析函式 least 及 日期函式MySql函式AST
- ORACLE函式介紹第六篇 著名函式之分析函式Oracle函式