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º¯Êý°´ÕÕij¸ö×Ö¶ÎÅÅÐòºóÈ¡µÃµÚÒ»ÐлòÕß×îºóÒ»ÐÐ,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
Ïà¹ØÎĵµ£º
¾Ñ飺
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²é¿´
......
1.´´½¨¹ý³Ì
¡¡¡¡¡¡ÓëÆäËüµÄÊý¾Ý¿âϵͳһÑù£¬OracleµÄ´æ´¢¹ý³ÌÊÇÓÃPL/SQLÓïÑÔ±àдµÄÄÜÍê³ÉÒ»¶¨´¦Àí¹¦ÄܵĴ洢ÔÚÊý¾Ý¿â×ÖµäÖеijÌÐò¡£
¡¡¡¡Óï·¨:
¡¡¡¡create [or replace] procedure procedure_name
¡¡¡¡[ (argment [ { in| in out }] type,
¡¡¡¡argment [ { in | out | in out } ] type
¡¡¡¡{ is | as }
¡¡¡¡<ÀàÐÍ ......
ÍÆ¼öcrs°æ±¾¸ßÓÚdb°æ±¾
If you also plan to apply this patch to Oracle Clusterware,
ensure that you apply the patch to Oracle Clusterware before
applying it to Oracle Database. This is because Oracle Clusterware
hould always be at the same or later version as the database.
Éæ¼°µ½Êý¾Ý¿â¶ø²»ÊÇʵÀýµ ......
---sqlµÄº¯ÊýµÄʹÓÃ(Oracle)
---dualµÄʹÓãºÑÆÔª±í£ºÃ»ÓбíÐèÒª²éѯµÄʱºò ¿ÉÒÔÓÃËü
---select 'Hello World' from dual; ---½á¹û£ºH ......
Ò»¡¢°²×°(ÂÔ)
·þÎñÆ÷¶Ë
1¡¢windows:£¨ÂÔ£©
2¡¢linux:´ýÐø...
¿Í»§¶Ë
´ýÐø...
--°üÀ¨ÅäÖÃ--
¶þ¡¢µÇ½
......