易截截图软件、单文件、免安装、纯绿色、仅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的archive模式

经验:
alter system set log_archive_dest=’D:\oracle\archivelog’ scope=spfile;
alter system set log_archive_start=true scope=spfile;
之后,
create pfile from spfile
可验证加上没
一、查看数据库运行模式 
可以用超级用户(INTERNAL)在SQLPLUS中使用命令ARCHIVE LOG LIST查看 
......

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 ' ......

ARCSDE for Oracle 数据存储形式为st_geometry

SELECT sde.st_area(zone) from sde.test1 ORDER BY name;//
SELECT shape from schools ORDER BY name;
SELECT objectid, sde.st_astext(SDE.ST_POINTfromSHAPE(shape,0)) AS points from schools;
SELECT name, sde.st_x (zone) "The X coordinate" from test ; //正确执行
SELECT name, sde.st_x (shape)  "The ......

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号