Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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º¯Êý°´ÕÕ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


Ïà¹ØÎĵµ£º

ORA 00054´íÎó OracleÊý¾Ý¿âµÄËø²Î¿¼×ÊÁÏ

½â¾ö·½°¸£º
    select session_id from v$locked_object;  --Ê×Ïȵõ½±»Ëø¶ÔÏóµÄsession_id
    SELECT sid, serial#, username, osuser from v$session where sid = session_id;   --ͨ¹ýÉÏÃæµÃµ½µÄsession_idȥȡµÃv$sessionµÄsidºÍserial#£¬È»ºó¶Ô¸Ã½ø³Ì½øÐÐÖÕÖ¹¡£
&n ......

Oracle ÍâÁ¬½ÓºÍ £¨+£©ºÅµÄÓ÷¨

¶ÔÓÚÍâÁ¬½Ó£¬OracleÖпÉÒÔʹÓÓ(+)”À´±íʾ£¬9i¿ÉÒÔʹÓÃLEFT/RIGHT/FULL OUTER JOIN£¬ÏÂÃæ½«ÅäºÏʵÀýÒ»Ò»½éÉÜ¡£
1. LEFT OUTER JOIN£º×óÍâ¹ØÁª
SELECT e.last_name, e.department_id, d.department_name 
from employees e 
LEFT OUTER JOIN department ......

Oracle Linux Æð動¤ÈÍ£Ö¹¤Î×Ô動»¯

¤½¤ÎËû¤Î¥ª¥Ú¥ìー¥Æ¥£¥ó¥°?¥·¥¹¥Æ¥à¤Ë¤ª¤±¤ë¥Çー¥¿¥Ùー¥¹¤ÎÆð動¤ÈÍ£Ö¹¤Î×Ô動»¯
dbstart¤ª¤è¤Ódbshut¥¹¥¯¥ê¥×¥È¤òʹÓ䷤ƥÇー¥¿¥Ùー¥¹¤ÎÆð動¤ÈÍ£Ö¹¤ò×Ô動»¯¤¹¤ë¤Ë¤Ï¡¢´Î¤ÎÊÖ順¤ò実ÐФ·¤Þ¤¹¡£
root¥æー¥¶ー¤Ç¥í¥°¥¤¥ó¤·¤Þ¤¹¡£
¥×¥é¥Ã¥È¥Õ ......

oracle 10g ¿ØÖÆÌ¨Æô¶¯¶Ë¿ÚÐÞ¸Ä

ÀýÈçÎÒÔÚoracle10gÀïÅäÖÃÁË2¸öÊý¾Ý¿âxljcºÍxldb,ÆÚ¿ØÖÆÌ«ä¯ÀÀÆ÷ÖÖÆô¶¯·Ö±ðΪ:xljcÔÚä¯ÀÀÆ÷ÖÐÊäÈë http://zhang:5500/em xldbÔÚä¯ÀÀÆ÷ÖÐÊäÈë http://zhang:1158/emÈç¹ûÍü¼ÇÁËÆô¶¯¶Ë¿ÚºÅ,¿ÉÒÔ²éѯÎļþ
ÀýÈçÎÒÔÚoracle10gÀïÅäÖÃÁË2¸öÊý¾Ý¿âxljcºÍxldb,ÆÚ¿ØÖÆÌ«ä¯ÀÀÆ÷ÖÖÆô¶¯·Ö±ðΪ:xljcÔÚä¯ÀÀÆ÷ÖÐÊäÈë http://zhang:5500/em
x ......

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ºÅ