易截截图软件、单文件、免安装、纯绿色、仅160KB

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


相关文档:

oracle管理表空间和数据文件

'
ALTER TABLESPACE app_data
      ADD DATAFILE 'u01/oradata/userdata03.dbf'
SIZE 200M;
'创建表空间
CREATE TABLESPACE userdata
DATAFILE 'u01/oradata/userdata03.dbf' SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;
'创建回滚表空间
CREATE UNDO TABLESPACE undo1
DATAFILE ' ......

Aix5.3下Oracle 10g设置归档模式到文件系统


新建文件系统
        crfs  -v jfs  -g  rootvg  -m /arch -a \ size=32768 -a frag=512 -a nbpi=8192  也可以 smit。
开启归档模式
     $ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 10 13:38 ......

Oracle学习笔记:linux下启停Oracle


1. linux下启动oracle
su - oracle
sqlplus /nolog
conn /as sysdba
startup
exit
lsnrctl start
exit
2. linux下关闭oracle
su - oracle
sqlplus /nolog
conn /as sysdba
shutdown immediate
exit
lsnrctl stop
exit
3、启动监听器
oracle@suse92:~> lsnrctl start
4、停止监听器
oracle@suse92:~ ......

ORACLE 的补丁下载地址 (转)

oracle补丁下载
关键字: oracle
9.2.0.4  = 3095277
9.2.0.5  = 3501955
9.2.0.6  = 3948480
9.2.0.7  = 4163445
9.2.0.8  = 4547809(9i最终)
10.1.0.3 = 3761843
10.1.0.4 = 4163362
10.1.0.5 = 4505133
10.2.0.2 = 4547817
10.2.0.3 = 5337014
10.2.0.4 = 6810189
下 ......

Oracle时间类型数据为0的bug

应用数据库中记录中date列莫名其妙的出现了几个值为0000-00-00的数据。
更可怕的是如果将这样的记录进行读取成为varchar2后再to_date转换回去就会报错。
问题无从查起,只说一下最终同事搜索到的连接结果:http://www.hellodba.com/Doc/oracle_date_zero_bug.htm
看来的确是oracle的一个bug。
另外需要注意的是,在使用 ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号