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

ORACLE GROUPINGº¯ÊýµÄʹÓÃ

GROUPINGº¯Êý¿ÉÒÔ½ÓÊÜÒ»ÁУ¬·µ»Ø0»òÕß1¡£Èç¹ûÁÐֵΪ¿Õ£¬ÄÇôGROUPING()·µ»Ø1£»Èç¹ûÁÐÖµ·Ç¿Õ£¬ÄÇô·µ»Ø0¡£GROUPINGÖ»ÄÜÔÚʹÓÃROLLUP»òCUBEµÄ²éѯÖÐʹÓᣵ±ÐèÒªÔÚ·µ»Ø¿ÕÖµµÄµØ·½ÏÔʾij¸öֵʱ£¬GROUPING()¾Í·Ç³£ÓÐÓá£
¹ØÓÚROLLUPºÍCUBEº¯ÊýµÄʹÓã¬Çë²Î¼ûÎÒµÄÁíһƪÎÄÕ¡£
http://blog.csdn.net/wh62592855/archive/2009/11/16/4817920.aspx
1¡¢ÔÚROLLUPÖжԵ¥ÁÐʹÓÃGROUPING()
SQL> select division_id,sum(salary)
  2  from employees2
  3  group by rollup(division_id)
  4  order by division_id;
DIV SUM(SALARY)
--- -----------
BUS     1610000
OPE     1320000
SAL     4936000
SUP     1015000
        8881000
¼ÓÉÏGROUPINGÀ´¿´¿´
SQL> select grouping(division_id),division_id,sum(salary)
  2  from employees2
  3  group by rollup(division_id)
  4  order by division_id;
GROUPING(DIVISION_ID) DIV SUM(SALARY)
--------------------- --- -----------
                    0 BUS     1610000
                    0 OPE     1320000
                    0 SAL     4936000
                    0 SUP     1015000
                    1         8881000
¿ÉÒÔ¿´µ½£¬Îª¿ÕµÄµØ·½·µ»Ø1£¬·Ç¿ÕµÄµØ·½·µ»Ø0¡£
2¡¢Ê¹ÓÃCASEת»»GROUPING()µÄ·µ»ØÖµ
¿ÉÄÜÄã»á¾õµÃÇ°ÃæµÄ0ºÍ1Ì«¿ÝÔïÁË£¬´ú±í²»ÁËÈκÎÒâÒ壬˵°×Á˾ÍÊDz»¹»ÈËÐÔ»¯£¬ºÇºÇ¡£Õâ¸öʱºòÎÒÃÇ¿ÉÒÔʹÓÃCASEÀ´×ª»»ÎªÒ


Ïà¹ØÎĵµ£º

ORACLE ÖÐROWNUMÓ÷¨×ܽá

2009-11-15  13:06:35
½ñÌìÔÚITPUB·¢ÏÖһƪÎÄÕ£¬ÀÏÔçÒÔÇ°µÄÁË£¬Ð´µÄÂùÓÐÒâ˼£¬ÌØÒâת¹ýÀ´Êղء£
¶ÔÓÚ Oracle µÄ rownum ÎÊÌ⣬ºÜ¶à×ÊÁ϶¼Ëµ²»Ö§³Ö>,>=,=,between...and£¬Ö»ÄÜÓÃÒÔÉÏ·ûºÅ(<¡¢<=¡¢!=)£¬²¢·Ç˵ÓÃ>, >=,=,between..and ʱ»áÌáʾSQLÓï·¨´íÎ󣬶øÊǾ­³£ÊDz鲻³öÒ»Ìõ¼Ç¼À´£¬»¹»á³öÏÖËƺ ......

OracleÃÜÂëÎļþµÄ×÷ÓúÍ˵Ã÷


ÔÚÊý¾Ý¿âδÆô¶¯Ö®Ç°µÇ½´Ó¶øÆô¶¯Êý¾Ý¿âÈç¹ûûÓпÚÁîÎļþ£¬ÔÚÊý¾Ý¿âδÆô¶¯Ö®Ç°¾ÍÖ»ÄÜͨ¹ý²Ù×÷ϵͳÈÏÖ¤.ʹÓÃRman£¬ºÜ¶àʱºòÐèÒªÔÚnomount,mountµÈ״̬¶ÔÊý¾Ý¿â½øÐд¦Àí¡£ËùÒÔͨ³£ÒªÇósysdbaȨÏÞÈç¹ûÊôÓÚ±¾µØDBA×飬¿ÉÒÔͨ¹ý²Ù×÷ϵͳÈÏÖ¤µÇ½Èç¹ûÊÇÔ¶³ÌsysdbaµÇ½£¬ÐèҪͨ¹ýpasswordfileÈÏÖ¤¡£
1.remote_login_passwordfile ......

Oracle Óï·¨Ö® OVER (PARTITION BY ..) ¼°¿ª´°º¯Êý


oracleµÄ·ÖÎöº¯Êýover ¼°¿ª´°º¯Êý
Ò»£º·ÖÎöº¯Êýover
Oracle´Ó8.1.6¿ªÊ¼Ìṩ·ÖÎöº¯Êý£¬·ÖÎöº¯ÊýÓÃÓÚ¼ÆËã»ùÓÚ×éµÄijÖÖ¾ÛºÏÖµ£¬ËüºÍ¾ÛºÏº¯ÊýµÄ²»Í¬Ö®´¦ÊÇ
¶ÔÓÚÿ¸ö×é·µ»Ø¶àÐУ¬¶ø¾ÛºÏº¯Êý¶ÔÓÚÿ¸ö×éÖ»·µ»ØÒ»ÐС£
ÏÂÃæͨ¹ý¼¸¸öÀý×ÓÀ´ËµÃ÷ÆäÓ¦Óᣠ          &nb ......

oracleÖÐconnect by priorʵÏֵݹé²éѯ

ÊÕ¼¯µÄ¼¸ÌõÔÚoracleÖÐͨ¹ýconnect by priorÀ´ÊµÏֵݹé²éѯ
Start with...Connect By×Ó¾äµÝ¹é²éѯһ°ãÓÃÓÚÒ»¸ö±íά»¤Ê÷ÐνṹµÄÓ¦Óá£
´´½¨Ê¾Àý±í£º
CREATE TABLE TBL_TEST
(
ID    NUMBER,
NAME VARCHAR2(100 BYTE),
PID   NUMBER         ......

oracle ÊÓƵ±Ê¼Ç3(insert¡¢update¡¢delete)

Ò»,INSERT
1.ΪÁ˲»´òÂÒÔ­À´µÄ±íµÄÊý¾Ý,ËùÒÔ±¸·ÝÔ­À´µÄÊý¾Ý.
create table emp2 as select * from emp
create table emp3 as select * from emp
create table dept2 as select * from dept
create table salgrade2 as select * from salgrade
2.²é¿´±íµÄÉè¼ÆÇé¿ö:desc dept2;±íʾ²é¿´±ídept2µÄÉè¼ÆÇé¿ö.
3.²åÈëÊý¾Ýµ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ