oracle keep(first/last)
先看一段ORACLE官方文档
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/analysis.htm#25806:
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函数按照某个字段排序后取得第一行或者最后一行,FIRST/LAST聚集函数可以按A列排序,B列聚集,避免了自连接和子查询.分组聚合函数(min,max....)位于FIRST/LAST函数之前产生多行结果集,并且按照排序返回FIRST/LAST单个值.
要指定在每个组的顺序，FIRST/LAST函数之前加上以关键字KEEP开始即可
FIRST/LAST Syntax
These functions have the following syntax:
aggregate_function KEEP
( DENSE_RANK LAST ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)
[OVER query_partitioning_clause]
Note that the ORDER BY clause can take multiple expressions.请注意在ORDER BY子句可以采取多种表现形式
Returns the row ranked first using DENSE_RANK
2种取值：
DENSE_RANK FIRST
DENSE_RANK LAST
在keep (DENSE_RANK first ORDER BY sl) 结果集中再取max、min的例子。
例子如下:oracle分析函数中，keep and over的区别
公司部门中入厂时间最早的员工的薪水最小的是多少
SQL>SELECT deptno,ename,empno,sal,
MIN(sal) KEEP (dense_rank FIRST ORDER BY hiredate) over (PARTITION
相关文档：
---sql的函数的使用(Oracle)
---dual的使用：哑元表：没有表需要查询的时候 可以用它
---select 'Hello World' from dual; ---结果：H ......
1.oracle环境
create table test
(
id number(4),
name varchar2(20)
)
在一个session中
insert into test values(1,'aa');
在另一个session中
select * from test1;
查询没被阻塞
2.sqlserver环境
在一个session中
insert into test values(1,'aa');
在另一个session中
select * from test1;
查询被阻塞(� ......
oracle 多表删除 同时删除多表中关联数据
2009-04-27 14:40
1、从数据表t1中把那些id值在数据表t2里有匹配的记录全删除掉
DELETE t1 from t1,t2 WHERE t1.id=t2.id 或DELETE from t1 USING t1,t2 WHERE t1.id=t2.id
2、从数据表t1里在数据表t2里没有匹配的记录查找出来并删除掉
DELETE t1 from t1 L ......
在Oracle8i或以上版本中，可以创建以下两种临时表：
1。会话特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)
&nbs ......
