Oracle中Keep的使用
官方文档有如下说明：FIRST/LAST Functions
The FIRST/LAST aggregate functions allow you to return the result of an aggregate applied over a set of rows that rank as the first or last with respect to a given order specification. FIRST/LAST lets you order on column A but return an result of an aggregate applied on column B. This is valuable because it avoids the need for a self-join or subquery, thus improving performance. These functions begin with a tiebreaker function, which is a regular aggregate function (MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV) that produces the return value. The tiebreaker function is performed on the set rows (1 or more rows) that rank as first or last respect to the order specification to return a single value.
To specify the ordering used within each group, the FIRST/LAST functions add a new clause starting with the word KEEP.
FIRST/LAST Syntax
These functions have the following syntax:
aggregate_function KEEP
( DENSE_RANK LAST ORDER BY
expr [ DESC | ASC ]
[, expr [ DESC | ASC ] ]...
)
-- emp表的数据SQL> SELECT t.empno,2 t.ename,3 t.mgr,4 t.sal,5 t.deptno6 FROM emp t7 ORDER BY t.sal,8 t.deptno; EMPNO ENAME MGR SAL DEPTNO---------- -------------------- ---------- ---------- ---------- 111 aaa 2222 800 9 7369 SMITH 7902 800 20 7900 JAMES 7698 950 30 7876 ADAMS 7788 1100 20 7521 WARD 7698 1250 30 7654 MARTIN 7698 1250 30 7934 MILLER 7782 1300 10 7844 TURNER 7698 1500 30 7499 ALLEN 7698 1600 30 7782 CLARK 7839 2450 10 7698 BLAKE 7839 2850 30 EMPNO ENAME MGR SAL DEPTNO---------- -------------------- ---------- ---------- ---------- 7566 JONES 7839 2975 20 7788 SCOTT 7566 3000 20 7902 FORD 7566 3000 20 7839 KING 5000 10 222 bbb 3333 5000 40-- 1.现在要查询表中工资最高的部门号的最大最小值,工资最低的部门号的最大最小值-- 因为是DENSE_RANK，会产生重复数据，使用min,max取一条。-- 这个sql没有使用over子句，后面的例子会使用SQL> SELECT MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) a,2 MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) b,3 MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) c,4 MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) d5 FROM emp t; A B C D---------- ---------- ---------- ---------- 9 20 10 40-- 2.加上over,对每一行记录做计算，看看效果：SQL> SQL> SELECT t.empno,2 t.ename,3 t.mgr,4 t.sal,5 t.deptno,6 MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER() a,7 MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER() b,8 MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER() c,9 MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER() d 10 FROM emp t 11 ORDER BY t.sal, 12 t.deptno 13;EMPNO ENAME MGR SAL DEPTNO A B C D----- ---------- ----- --------- ------ ---------- ---------- ---------- ----------111 aaa 2222 800.00 9 9 20 10 40 7369 SMITH 7902 800.00 20 9 20 10 40 7900 JAMES 7698 950.00 30 9 20 10 40 7876 ADAMS 7788 1100.00 20 9 20 10 40 7521 WARD 7698 1250.00 30 9 20 10 40 7654 MARTIN 7698 1250.00 30 9 20 10 40 7934 MILLER 7782 1300.00 10 9 20 10 40 7844 TURNER 7698 1500.00 30 9 20 10 40 7499 ALLEN 7698 1600.00 30 9 20 10 40 7782 CLARK 7839 2450.00 10 9 20 10 40 7698 BLAKE 7839 2850.00 30 9 20 10 40 7566 JONES 7839 2975.00 20 9 20 10 40 7788 SCOTT 7566 3000.00 20 9 20 10 40 7902 FORD 7566 3000.00 20 9 20 10 40 7839 KING 5000.00 10 9 20 10 40222 bbb 3333 5000.00 40 9 20 10 40-- 3.下面对每一个mgr求最大(最小)工资的部门号的最大(最小)值SQL> SELECT t.empno,2 t.ename,3 t.mgr,4 t.sal,5 t.deptno,6 MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER(PARTITION BY t.mgr) a,7 MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER(PARTITION BY t.mgr) b,8 MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER(PARTITION BY t.mgr) c,9 MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER(PARTITION BY t.mgr) d 10 FROM emp t 11 ORDER BY t.sal, 12 t.deptno 13;EMPNO ENAME MGR SAL DEPTNO A B C D----- ---------- ----- --------- ------ ---------- ---------- ---------- ----------111 aaa 2222 800.00 9 9 9 9 9 7369 SMITH 7902 800.00 20 20 20 20 20 7900 JAMES 7698 950.00 30 30 30 30 30 7876 ADAMS 7788 1100.00 20 20 20 20 20 7654 MARTIN 7698 1250.00 30 30 30 30 30 7521 WARD 7698 1250.00 30 30 30 30 30 7934 MILLER 7782 1300.00 10 10 10 10 10 7844 TURNER 7698 1500.00 30 30 30 30 30 7499 ALLEN 7698 1600.00 30 30 30 30 30 7782 CLARK 7839 2450.00 10 10 10 20 20 7698 BLAKE 7839 2850.00 30 10 10 20 20 7566 JONES 7839 2975.00 20 10 10 20 20 7902 FORD 7566 3000.00 20 20 20 20 20 7788 SCOTT 7566 3000.00 20 20 20 20 20 7839 KING 5000.00 10 10 10 10 10222 bbb 3333 5000.00 40 40 40 40 40
页:
[1]