±Ê¼Ç£ºOracle³£Ó÷ÖÎöº¯Êý
Oracle³£Ó÷ÖÎöº¯Êý
ROW_NUMBER
·µ»ØÓÐÐò×éÖÐÒ»ÐÐµÄÆ«ÒÆÁ¿£¬´Ó¶ø¿ÉÓÃÓÚ°´Ìض¨±ê×¼ÅÅÐòµÄÐкÅ
row_number() over(partition by ... order by ...)
RANK
¸ù¾ÝORDER BY×Ó¾äÖбí´ïʽµÄÖµ£¬´Ó²éѯ·µ»ØµÄÿһÐУ¬¼ÆËãËüÃÇÓëÆäËüÐеÄÏà¶ÔλÖá£×éÄÚµÄÊý¾Ý°´ORDER BY×Ó¾äÅÅÐò£¬È»ºó¸øÃ¿Ò»Ðи³Ò»¸öºÅ£¬´Ó¶øÐγÉÒ»¸öÐòÁУ¬¸ÃÐòÁдÓ1¿ªÊ¼£¬ÍùºóÀÛ¼Ó¡£Ã¿´ÎORDER BY±í´ïʽµÄÖµ·¢Éú±ä»¯Ê±£¬¸ÃÐòÁÐÒ²ËæÖ®Ôö¼Ó¡£ÓÐͬÑùÖµµÄÐеõ½Í¬ÑùµÄÊý×ÖÐòºÅ£¨ÈÏΪnullʱÏàµÈµÄ£©¡£È»¶ø£¬Èç¹ûÁ½ÐеÄÈ·µÃµ½Í¬ÑùµÄÅÅÐò£¬ÔòÐòÊý½«ËæºóÌøÔ¾¡£ÈôÁ½ÐÐÐòÊýΪ1£¬ÔòûÓÐÐòÊý2£¬ÐòÁн«¸ø×éÖеÄÏÂÒ»ÐзÖÅäÖµ3£¬DENSE_RANKÔòûÓÐÈκÎÌøÔ¾
rank() over(partition by ... order by ...)
DENSE_RANK
¸ù¾ÝORDER BY×Ó¾äÖбí´ïʽµÄÖµ£¬´Ó²éѯ·µ»ØµÄÿһÐУ¬¼ÆËãËüÃÇÓëÆäËüÐеÄÏà¶ÔλÖá£×éÄÚµÄÊý¾Ý°´ORDER BY×Ó¾äÅÅÐò£¬È»ºó¸øÃ¿Ò»Ðи³Ò»¸öºÅ£¬´Ó¶øÐγÉÒ»¸öÐòÁУ¬¸ÃÐòÁдÓ1¿ªÊ¼£¬ÍùºóÀÛ¼Ó¡£Ã¿´ÎORDER BY±í´ïʽµÄÖµ·¢Éú±ä»¯Ê±£¬¸ÃÐòÁÐÒ²ËæÖ®Ôö¼Ó¡£ÓÐͬÑùÖµµÄÐеõ½Í¬ÑùµÄÊý×ÖÐòºÅ£¨ÈÏΪnullʱÏàµÈµÄ£©¡£Ãܼ¯µÄÐòÁзµ»ØµÄʱûÓмä¸ôµÄÊý
dense_rank() over(partition by ... order by ...)
COUNT
¶ÔÒ»×éÄÚ·¢ÉúµÄÊÂÇé½øÐÐÀÛ»ý¼ÆÊý£¬Èç¹ûÖ¸¶¨*»òһЩ·Ç¿Õ³£Êý£¬count½«¶ÔËùÓÐÐмÆÊý£¬Èç¹ûÖ¸¶¨Ò»¸ö±í´ïʽ£¬count·µ»Ø±í´ïʽ·Ç¿Õ¸³ÖµµÄ¼ÆÊý£¬µ±ÓÐÏàֵͬ³öÏÖʱ£¬ÕâЩÏàµÈµÄÖµ¶¼»á±»ÄÉÈë±»¼ÆËãµÄÖµ£»¿ÉÒÔʹÓÃDISTINCTÀ´¼Ç¼ȥµôÒ»×éÖÐÍêÈ«ÏàͬµÄÊý¾Ýºó³öÏÖµÄÐÐÊý
count(...) over(partition by ... order by ...)
MAX
ÔÚÒ»¸ö×éÖеÄÊý¾Ý´°¿ÚÖвéÕÒ±í´ïʽµÄ×î´óÖµ
max(...) over(partition by ... order by ...)
MIN
ÔÚÒ»¸ö×éÖеÄÊý¾Ý´°¿ÚÖвéÕÒ±í´ïʽµÄ×îСֵ
min(...) over(partition by ... order by ...)
SUM
¸Ãº¯Êý¼ÆËã×éÖбí´ïʽµÄÀÛ»ýºÍ
sum(...) over(partition by ... order by ...)
AVG
ÓÃÓÚ¼ÆËãÒ»¸ö×éºÍÊý¾Ý´°¿ÚÄÚ±í´ïʽµÄƽ¾ùÖµ
avg(...) over(partition by ... order by ...)
FIRST_VALUE
·µ»ØÊý¾Ý×éÖеÚÒ»¸öÖµ
first_value(...) over(partition by ... order by ...)
LAST_VALUE
·µ»ØÊý¾Ý×éÖÐ×îºóÒ»¸öÖµ
last_value(...) over(partition by ... order by ...)
LAG
¿ÉÒÔ·ÃÎʽá¹û¼¯ÖÐµÄÆäËüÐжø²»ÓýøÐÐ×ÔÁ¬½Ó¡£ËüÔÊÐíÈ¥´¦ÀíÓα꣬¾ÍºÃÏñÓαêÊÇÒ»¸öÊý×éÒ»Ñù¡£ÔÚ¸ø¶¨×éÖпɲο¼µ±Ç°ÐÐ֮ǰµÄÐУ¬ÕâÑù¾Í¿ÉÒÔ´Ó×éÖÐÓ뵱ǰÐÐÒ»ÆðÑ¡ÔñÒÔǰµÄÐС£OffsetÊÇÒ»¸öÕýÕûÊý
Ïà¹ØÎĵµ£º
¿ªÊ¼ÔÚmyeclipse6.5ÖÐDB BROWERн¨ÁËÒ»¸öÊý¾Ý¿âÁ¬½Ó£¬Èçͼ£º
Ò»Á¬½Ó£¬±¨³öÁËÁ½¸ö´íÎó£º
1. ORA-00604 error occurred at recursive SQL level string.
2.ORA-12705: invalid or unknown NLS parameter value.
²é×ÊÁϺó½â¾ö£¨ÆÚ¼äÎÒ»¹¸Ä¹ýtestÓû§µÄÁ¬½ÓȨÏÞ£©£º
½«MyEclipseÖÐeclipseĿ¼ÏµÄeclipse.iniÎļþ£¬° ......
ÈçºÎ±¸·ÝOracleÊý¾Ý¿â£º
MD F:\DB_BAK\%date:~0,10%
EXP userid=bszlhr/bszlhr@ORCL_14 file=F:\DB_BAK\%date:~0,10%\bszlhr%date:~0,10%.dmp log=f:\DB_BAK\%date:~0,10%\log_bszlhr_%date:~0,10%.log
buffer=64000 owner=bszlhr
-----´´½¨ÐÅÓÃÐÅÏ¢µÄ±í----------
create table GCCredit(
itemID numb ......
1¡¢¹Ì¶¨ÁÐÊýµÄÐÐÁÐת»»
Èç
student subject grade
--------- ---------- --------
student1 ÓïÎÄ 80
student1 Êýѧ 70
student1 Ó¢Óï 60
student2 ÓïÎÄ 90
student2 Êýѧ 80
student2 Ó¢Óï 100
……
ת»»Îª
ÓïÎÄ Êýѧ Ó¢Óï
student1 80 70 60
student2 90 80 100
……
Óï¾äÈçÏ£ºs ......
½ñÌìÉÏÎç²âÊÔÒ»¸ö·ÃÎÊORACLEµÄc++À࣬ÎĵµÉÏ˵Á¬½Ó×Ö·û´®µÄ¸ñʽΪ"Óû§Ãû/¿ÚÁî@Á¬½ÓÃû"£¬ÎÒ²»ÊÇÌ«Ã÷°×Á¬½ÓÃûµ½µ×ΪºÎÎÏÈÓÃIPµØÖ·ÊÔÁËÊÔ
£¬×ÜÊDZ¨´í£¬ËµÎÞ·¨½âÎöµÄÁ¬½Ó±êʶ·û£¬ºóÀ´ÔÚÍøÉϲéÁ˰ëÌ죬¿´µ½ÓиöÈË˵Á¬½ÓÃû¾ÍÊÇ$(ORACLE_HOME)/network/admin/tnsnames.oraÀﶨÒåµÄÊý¾Ý¿âÁ¬½ÓµÄÃû³Æ£¬ÊÔÁËһϣ¬¹ûÈ»Èç´Ë¡£ ......
ÓÃJavaÀ´²Ù×÷Êý¾Ý¿â£¬ÓÐÈçϼ¸ÖÖ·½Ê½£º
1JDBC-ODBCÇÅ£ºODBCÊý¾ÝÔ´ÔÚ²Ù×÷ϵͳ²ãÃæ¸øÓèÁËÖ§³Ö£¬ÎÒÃÇ¿ÉÒÔ²»ÓÃÁ˽âµ×²ãµ½µ×ÊÇ·â×°µÄÄÄÖÖÊý¾ÝÔ´£¬Ò²ÕÕÑù¿ÉÒÔÍê³É¶ÔÊý¾ÝµÄ²Ù×÷¡£µ«ÊÇËüÓÐÆ½Ì¨ÒÀÀµÐÔ£¬²»Ì«ÍƼöʹÓá£
2Javaµ½±¾µØAPI
3Javaµ½ÍøÂçÐÒé
4Javaµ½Êý¾Ý¿âÐÒ飺ËÙ¶È×î¿ì
Á¬½ÓÊý¾Ý¿â¿ÉÒÔ·ÖΪËIJ½£º
A¼ÓÔØÇý¶¯³ÌÐò£¨Ï ......