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Êý¾Ý¿âµÄ´æ´¢¹ý³ÌʵÏÖ·µ»Ø½á¹û¼¯²¢ÊµÏÖ·ÖÒ³µÄ¹¦ÄÜ¡£
Óû§´«Èë²ÎÊý
Ò»ÏÂÊÇת±ðÈ˵ĴúÂë
--°üÉùÃ÷
create or replace package p_page is
-- Author : PHARAOHS
-- Created : 2006-4-30 14:14:14
-- Purpose : ·ÖÒ³¹ý³Ì
TYPE type_cur IS REF CURSOR; &n ......
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 ......
1¡¢ORA£12541£ºTNS£ºÃ»ÓмàÌýÆ÷ ¡¡¡¡
ÔÒò£ºÃ»ÓÐÆô¶¯¼àÌýÆ÷»òÕß¼àÌýÆ÷Ë𻵡£Èç¹ûÊÇǰÕߣ¬Ê¹ÓÃÃüÁînet start OracleOraHome81TNSListener£¨Ãû×Ö¿ÉÄÜÓгöÈ룩¼´¿É£»Èç¹ûÊǺóÕߣ¬ÔòʹÓÓNet8 Configuration Assistant”¹¤¾ßÏòµ¼Ö®“¼àÌý³ÌÐòÅäÖÔÔö¼ÓÒ»¸ö¼àÌýÆ÷¼´¿É£¨»ù±¾²» ......