Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

ORACLE Top/Bottom N¡¢First/Last¡¢NTile

Ŀ¼
==================================================================
1.´ø¿ÕÖµµÄÅÅÁÐ
2.Top/Bottom N²éѯ
3.First/LastÅÅÃû²éѯ
4.°´²ã´Î²éѯ
Ò»¡¢´ø¿ÕÖµµÄÅÅÁУº
¼ÙÈç±»ÅÅÁеÄÊý¾ÝÖк¬ÓпÕÖµÄØ£¿
SQL> select region_id, customer_id,
   2         sum(customer_sales) cust_sales,
   3         sum(sum(customer_sales)) over(partition by region_id) ran_total,
   4         rank() over(partition by region_id
   5                  order by sum(customer_sales) desc) rank
   6    from user_order
   7   group by region_id, customer_id;
REGION_ID CUSTOMER_ID CUST_SALES RAN_TOTAL       RANK
---------- ----------- ---------- ---------- ----------
         10          31               6238901          1
         10          26    1808949    6238901          2
         10          27    1322747    6238901          3
         10          30    1216858    6238901          4
         10          28 


Ïà¹ØÎĵµ£º

Oracle Dimension £¨Oracle ά£©

 
ÔÚÊý¾Ý²Ö¿â»·¾³ÖУ¬ÎÒÃÇͨ³£ÀûÓÃÎﻯÊÓͼǿ´óµÄ²éѯÖØд¹¦ÄÜÀ´ÌáÉýͳ¼Æ²éѯµÄÐÔÄÜ£¬µ«ÊÇÎﻯÊÓͼµÄ²éѯÖØд¹¦ÄÜÓÐʱºòÎÞ·¨ÖÇÄܵØÅжϲéѯÖÐһЩÏà¹ØÁªµÄÌõ¼þ£¬ÒÔÖÁÓÚÓ°ÏìÐÔÄÜ¡£±ÈÈçÎÒÃÇÓÐÒ»ÕÅÏúÊÛ±ísales£¬ÓÃÓÚ´æ´¢¶©µ¥µÄÏêϸÐÅÏ¢£¬°üº¬½»Ò×ÈÕÆÚ¡¢¹Ë¿Í±àºÅºÍÏúÊÛÁ¿¡£ÎÒÃÇ´´½¨Ò»ÕÅÎﻯÊÓͼ£¬°´Ô´洢ÀÛ¼ÆÏúÁ¿ÐÅÏ¢£¬¼ ......

ORACLEÖÐin ºÍ existsÇø±ð

INºÍEXISTSÇø±ð
in ÊÇ°ÑÍâ±íºÍÄÚ±í×÷hash join£¬¶øexistsÊǶÔÍâ±í×÷loop£¬Ã¿´ÎloopÔÙ¶ÔÄÚ±í½øÐвéѯ¡£
Ò»Ö±ÒÔÀ´ÈÏΪexists±ÈinЧÂʸߵÄ˵·¨ÊDz»×¼È·µÄ¡£
Èç¹û²éѯµÄÁ½¸ö±í´óСÏ൱£¬ÄÇôÓÃinºÍexists²î±ð²»´ó¡£
Èç¹ûÁ½¸ö±íÖÐÒ»¸ö½ÏС£¬Ò»¸öÊÇ´ó±í£¬Ôò×Ó²éѯ±í´óµÄÓÃexists£¬×Ó²éѯ±íСµÄÓÃin£º
ÀýÈ磺±íA£¨Ð¡±í£©£¬±íB ......

Oracle trim º¯ÊýµÄÓ÷¨

 select trim(leading | trailing | both '  ' from '   abc      d      ') from dual;
 È¥µô×Ö·û´® '   abc      d      ' µÄÇ°Ãæ/ºóÃæ/Ç°ºóµÄ¿Õ¸ñ
 ÀàËƺ¯Êý£ºltrim, ......

ORACLEÈçºÎʹÓÃDBMS_METADATA.GET_DDL»ñÈ¡DDLÓï¾ä

1.µÃµ½Ò»¸ö±íµÄddlÓï¾ä£º
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999             ------ÏÔʾ²»ÍêÕû
SET PAGESIZE 1000    ----·ÖÒ³
 
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.S ......

¡¾ÊÕ²ØÕûÀí¡¿OracleÊý¾Ý¿âÌåϵ¼Ü¹¹

 Ô­Îļûhttp://blog.csdn.net/kele1121/archive/2009/10/30/4742051.aspxÓëhttp://www.itpub.net/thread-1105403-1-1.html
 Ëùν
Oracle
µÄÌåϵ¼Ü¹¹£¬ÊÇÖ¸
Oracle
Êý¾Ý¿â¹ÜÀíϵͳµÄµÄ×é³É²¿·ÖºÍÕâЩ×é³É²¿·ÖÖ®¼äµÄÏ໥¹Øϵ£¬°üÀ¨
ÄÚ´æ½á¹¹¡¢ºǫ́½ø³Ì¡¢ÎïÀíÓëÂß¼­½á¹¹µÈ¡£
Oracle
Êý¾Ý¿âµÄÌåϵºÜ¸´ÔÓ£¬¸´Ô ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ