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
Ïà¹ØÎĵµ£º
½â¾ö·½°¸£º
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ÖпÉÒÔʹÓÓ(+)”À´±íʾ£¬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 ......
¤½¤ÎËû¤Î¥ª¥Ú¥ìー¥Æ¥£¥ó¥°?¥·¥¹¥Æ¥à¤Ë¤ª¤±¤ë¥Çー¥¿¥Ùー¥¹¤ÎÆð動¤ÈÍ£Ö¹¤Î×Ô動»¯
dbstart¤ª¤è¤Ódbshut¥¹¥¯¥ê¥×¥È¤òʹÓ䷤ƥÇー¥¿¥Ùー¥¹¤ÎÆð動¤ÈÍ£Ö¹¤ò×Ô動»¯¤¹¤ë¤Ë¤Ï¡¢´Î¤ÎÊÖ順¤ò実ÐФ·¤Þ¤¹¡£
root¥æー¥¶ー¤Ç¥í¥°¥¤¥ó¤·¤Þ¤¹¡£
¥×¥é¥Ã¥È¥Õ ......
ÀýÈçÎÒÔÚoracle10gÀïÅäÖÃÁË2¸öÊý¾Ý¿âxljcºÍxldb,ÆÚ¿ØÖÆÌ«ä¯ÀÀÆ÷ÖÖÆô¶¯·Ö±ðΪ:xljcÔÚä¯ÀÀÆ÷ÖÐÊäÈë http://zhang:5500/em xldbÔÚä¯ÀÀÆ÷ÖÐÊäÈë http://zhang:1158/emÈç¹ûÍü¼ÇÁËÆô¶¯¶Ë¿ÚºÅ,¿ÉÒÔ²éѯÎļþ
ÀýÈçÎÒÔÚoracle10gÀïÅäÖÃÁË2¸öÊý¾Ý¿âxljcºÍxldb,ÆÚ¿ØÖÆÌ«ä¯ÀÀÆ÷ÖÖÆô¶¯·Ö±ðΪ:xljcÔÚä¯ÀÀÆ÷ÖÐÊäÈë http://zhang:5500/em
x ......
Ó¦ÓÃÊý¾Ý¿âÖмǼÖÐdateÁÐĪÃûÆäÃîµÄ³öÏÖÁ˼¸¸öֵΪ0000-00-00µÄÊý¾Ý¡£
¸ü¿ÉŵÄÊÇÈç¹û½«ÕâÑùµÄ¼Ç¼½øÐжÁÈ¡³ÉΪvarchar2ºóÔÙto_dateת»»»ØÈ¥¾Í»á±¨´í¡£
ÎÊÌâÎÞ´Ó²éÆð£¬Ö»ËµÒ»ÏÂ×îÖÕͬÊÂËÑË÷µ½µÄÁ¬½Ó½á¹û£ºhttp://www.hellodba.com/Doc/oracle_date_zero_bug.htm
¿´À´µÄÈ·ÊÇoracleµÄÒ»¸öbug¡£
ÁíÍâÐèҪעÒâµÄÊÇ£¬ÔÚʹÓà ......