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

Oracle ROLLUPºÍCUBE Ó÷¨

ÔÚ×öÏîÄ¿¾­³£Óöµ½·Ö¿ÆÊÒ¡¢ÈËÔ±½øÐлã×ܵÄÎÊÌ⣬ÔÚORACLEÖжԴËÀàÎÊÌâµÄ´¦ÀíÏ൱·½±ã£¡ÏÂÃæÒÔÏîÄ¿ÖÐÓöµ½µÄʵÀý½øÐÐ˵Ã÷£º
²éѯÓï¾äÈçÏ£º
select f_sys_getsectnamebysectid(a.sectionid) as sectname,
        --a.sectionid,
       f_sys_employinfo_getnamebyno(a.buyerid) as name,
       sum(c.ratio) as llratio,
       sum(b.ratio) as lratio,
       sum(a.ratio) as ratio,
       sum(d.ratio) as nratio
  from (select sectionid, buyerid, ratio
          from T_PPRM_ASPPurRatio
         where year = '2008') a,
       (select buyerid, ratio from T_PPRM_ASPPurRatio where year = '2007') b,
       (select buyerid, ratio from T_PPRM_ASPPurRatio where year = '2006') c,
       (select buyerid, ratio from T_PPRM_ASPPurRatio where year = '2009') d
 where a.buyerid = b.buyerid(+)
   and b.buyerid = c.buyerid(+)
   and c.buyerid = d.buyerid(+)
 group by rollup(a.sectionid, a.buyerid)
ÏÂÃæ×ªÌû¸üÏêϸµÄ½éÉÜ£¬Ðè×ÐϸÌå»á
Oracle ROLLUPºÍCUBE Ó÷¨    
OracleµÄGROUP BYÓï¾ä³ýÁË×î»ù±¾µÄÓï·¨Í⣬»¹Ö§³ÖROLLUPºÍCUBEÓï¾ä¡£Èç¹ûÊÇROLLUP(A, B, C)µÄ»°£¬Ê×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP BY£¬È»ºó¶Ô(A¡¢B)½øÐÐGROUP BY£¬È»ºóÊÇ(A)½øÐÐGROUP BY£¬×îºó¶ÔÈ«±í½øÐÐGROUP BY²Ù×÷¡£Èç¹ûÊÇGROUP BY CUBE(A, B, C)£¬ÔòÊ×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP BY£¬È»ºóÒÀ´ÎÊÇ(A¡¢B)£¬(A¡¢C)£¬(A)£¬(B¡¢C)£¬(B)£¬(C)£¬×îºó¶ÔÈ«±í½øÐÐGROUP BY²Ù×÷¡£ grouping_id()¿ÉÒÔÃÀ»¯Ð§¹û£º
OracleµÄGROUP BYÓï¾ä³ýÁË×î»ù±¾µÄÓï·¨Í⣬»¹Ö§³ÖROLLUPºÍCUBEÓï¾ä¡£
³ý±¾ÎÄÄÚÈÝÍ⣬Ä㻹¿É²Î¿¼£º
·ÖÎöº¯Êý²Î¿¼Êֲ᣺ http://xsb.itpub.net/post/419/33028
·ÖÎöº¯ÊýʹÓÃÀý×Ó½éÉÜ£ºhttp://xsb.itpub.net/post/419/44634
SQL> create table t as select * from dba_indexes;
±


Ïà¹ØÎĵµ£º

oracle ´æ´¢¹ý³ÌʵÏÖ·ÖÒ³

  ÓÃoracleÊý¾Ý¿âµÄ´æ´¢¹ý³ÌʵÏÖ·µ»Ø½á¹û¼¯²¢ÊµÏÖ·ÖÒ³µÄ¹¦ÄÜ¡£
Óû§´«Èë²ÎÊý
Ò»ÏÂÊÇת±ðÈ˵ĴúÂë
--°üÉùÃ÷
create or replace package p_page is
  -- Author  : PHARAOHS
  -- Created : 2006-4-30 14:14:14
  -- Purpose : ·ÖÒ³¹ý³Ì
  TYPE type_cur IS REF CURSOR;  &n ......

oracle»ù±¾²Ù×÷±¸Íü


1. ²éѯÊý¾Ý¿âÏÖÔڵıí¿Õ¼ä
select tablespace_name, file_name, sum(bytes)/1024/1024 table_size from dba_data_files group by tablespace_name,file_name;
2. ½¨Á¢±í¿Õ¼ä
CREATE TABLESPACE data01 DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M; 
3.ɾ³ý±í¿Õ¼ä
DROP TABLESPACE data01 INCLUDING C ......

ʹÓÃOracleÊý¾Ý¿â¹ý³ÌÖг£¼û´íÎó

1¡¢ORA£­12541£ºTNS£ºÃ»ÓмàÌýÆ÷ ¡¡¡¡
     Ô­Òò£ºÃ»ÓÐÆô¶¯¼àÌýÆ÷»òÕß¼àÌýÆ÷Ë𻵡£Èç¹ûÊÇǰÕߣ¬Ê¹ÓÃÃüÁînet start OracleOraHome81TNSListener£¨Ãû×Ö¿ÉÄÜÓгöÈ룩¼´¿É£»Èç¹ûÊǺóÕߣ¬ÔòʹÓÓNet8 Configuration Assistant”¹¤¾ßÏòµ¼Ö®“¼àÌý³ÌÐòÅäÖÔÔö¼ÓÒ»¸ö¼àÌýÆ÷¼´¿É£¨»ù±¾²» ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ