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

¡¾×ª¡¿ Oracle group by¼°ÆäÈô¸ÉÏà¹Øº¯ÊýµÄһЩ˵Ã÷

Oracle group by¼°ÆäÈô¸ÉÏà¹Øº¯ÊýµÄһЩ˵Ã÷
http://blog.csdn.net/roland_wg/archive/2009/07/03/4319323.aspx
OracleµÄgroup by³ýÁË»ù±¾Ó÷¨ÒÔÍ⣬»¹ÓÐ3ÖÖÀ©Õ¹Ó÷¨£¬·Ö±ðÊÇrollup¡¢cube¡¢grouping sets¡£
¼ÙÉèÓÐÒ»¸ö±ítest£¬ÓÐA¡¢B¡¢C¡¢D¡¢E5ÁС£
1£© Èç¹ûʹÓÃgroup by rollup(A,B,C)£¬Ê×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP BY£¬È»ºó¶Ô(A¡¢B)½øÐÐGROUP BY£¬È»ºóÊÇ(A)½øÐÐGROUP BY£¬×îºó¶ÔÈ«±í½øÐÐGROUP BY²Ù×÷¡£roll upµÄÒâ˼ÊÇ“¾íÆð”£¬ÕâÒ²¿ÉÒÔ°ïÖúÎÒÃÇÀí½âgroup by rollup¾ÍÊǶÔÑ¡ÔñµÄÁдÓÓÒµ½×óÒÔÒ»´ÎÉÙÒ»Áеķ½Ê½½øÐÐgroupingÖ±µ½ËùÓÐÁж¼È¥µôºóµÄgrouping(Ò²¾ÍÊÇÈ«±ígrouping)£¬¶ÔÓÚn¸ö²ÎÊýµÄrollup£¬ÓÐn+1´ÎµÄgrouping¡£ÒÔÏÂ2¸ösqlµÄ½á¹û¼¯ÊÇÒ»ÑùµÄ£º
Select A,B,C,sum(E) from test group by rollup(A,B,C)ºÍ
Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,null,null,sum(E) from test
2) cubeµÄÒâ˼ÊÇÁ¢·½£¬¶ÔcubeµÄÿ¸ö²ÎÊý£¬¶¼¿ÉÒÔÀí½âΪȡֵΪ²ÎÓëgroupingºÍ²»²ÎÓëgroupingÁ½¸öÖµµÄÒ»¸öά¶È£¬È»ºóËùÓÐά¶Èȡֵ×éºÏµÄ¼¯ºÏ¾ÍÊÇgroupingµÄ¼¯ºÏ£¬¶ÔÓÚn¸ö²ÎÊýµÄcube£¬ÓÐ2^n´ÎµÄgrouping¡£Èç¹ûʹÓÃgroup by cube(A,B,C), £¬ÔòÊ×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP BY£¬È»ºóÒÀ´ÎÊÇ(A¡¢B)£¬(A¡¢C)£¬(A)£¬(B¡¢C)£¬(B)£¬(C)£¬×îºó¶ÔÈ«±í½øÐÐGROUP BY²Ù×÷£¬Ò»¹²ÊÇ2^3=8´Îgrouping¡£Í¬rollupÒ»Ñù£¬Ò²¿ÉÒÔÓûù±¾µÄgroup by¼ÓÉϽá¹û¼¯µÄunion allд³öÒ»¸öÓëgroup by cube½á¹û¼¯ÏàͬµÄsql£º
Select A,B,C,sum(E) from test group by cube(A,B,C)£»
Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,C,sum(E) from test group by A,C
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,B,C,sum(E) from test group by B,C
union all
Select null,B,null,sum(E) from test group by B
union all
Select null,null,C,sum(E) from test group by C
union all
Select null,null,null,sum(E) from test
3) grouping sets¾ÍÊǶԲÎÊýÖеÄÿ¸ö²ÎÊý×ögrouping£¬Ò²¾ÍÊÇÓм¸¸ö²ÎÊý×ö¼¸´Îgrouping, ÀýÈçʹÓÃgroup by grouping sets(A,B,C)£¬Ôò¶Ô(A),(B),(C)½øÐÐgroup by£¬È


Ïà¹ØÎĵµ£º

OracleµÄÓû§¹ÜÀí ORA

ÅäÖÃÉí·ÝÑéÖ¤
OracleΪÓû§ÕË»§ÌṩÈýÖÖÉí·ÝÑéÖ¤·½·¨£º
(1)ÃÜÂëÑéÖ¤
µ±Ò»¸öʹÓÃÃÜÂëÑéÖ¤»úÖÆµÄÓû§ÊÔͼÁ¬½Óµ½Êý¾Ý¿âʱ£¬Êý¾Ý¿â»áºËʵÓû§ÃûÊÇ·ñÊÇÒ»¸öÓÐЧµØÊý¾Ý¿âÓû§£¬²¢ÇÒÌṩÓë¸ÃÓû§ÔÚÊý¾Ý¿âÖд洢µÄÃÜÂëÏàÆ¥ÅäµÄÃÜÂë¡£
(2)ÍⲿÑéÖ¤
µ±Ò»¸öÍⲿÑé֤ʽÓû§ÊÔͼÁ¬½Óµ½Êý¾Ý¿âʱ£¬Êý¾Ý¿â»áºËʵÓû§ÃûÊÇ·ñÊÇÒ»¸öÓÐЧµÄ ......

oracleÈÕÆÚʱ¼äº¯Êý´óÈ«

TO_DATE¸ñʽ(ÒÔʱ¼ä:2007-11-02   13:45:25ΪÀý)
  
        Year:     
        yy two digits Á½Î»Äê               ......

ORACLE JOB ÉèÖÃ

                   
    JobµÄ²ÎÊý£º
    Ò»£ºÊ±¼ä¼ä¸ôÖ´ÐУ¨Ã¿·ÖÖÓ£¬Ã¿Ì죬ÿÖÜ£¬:ÿÔ£¬Ã¿¼¾¶È£¬Ã¿°ëÄ꣬ÿÄ꣩
   intervalÊÇÖ¸ÉÏÒ»´ÎÖ´ÐнáÊøµ½ÏÂÒ»´Î¿ªÊ¼Ö´ÐеÄʱ¼ä¼ ......

oracle¼òµ¥¶ÔÏóÀàÐÍ

ÔÚplsqlÖУ¬ÃæÏò¶ÔÏóµÄ³ÌÐòÉè¼ÆÊÇ»ùÓÚ¶ÔÏóÀàÐÍÀ´Íê³ÉµÄ¡£¶ÔÏóÀàÐÍÊÇÓû§×Ô¶¨ÒåµÄÒ»ÖÖ¸´ºÏÊý¾ÝÀàÐÍ£¬Ëü·â×°ÁËÊý¾Ý½á¹¹ºÍÓÃÓÚ²Ù×ÝÕâЩÊý¾Ý½á¹¹µÄ¹ý³ÌºÍº¯Êý£¬ÔÚ½¨Á¢¸´ÔÓÓ¦ÓóÌÐòʱ£¬Í¨¹ýʹÓöÔÏóÀàÐÍ¿ÉÒÔ½µµÍÓ¦Óÿª·¢ÄѶȣ¬½ø¶øÌá¸ßÓ¦Óÿª·¢µÄЧÂʺÍËÙ¶È¡£
¶ÔÏóÀàÐͰüÀ¨¶ÔÏóÀàÐ͹淶£¨object type specification£©ºÍ¶ÔÏóÀàÐÍ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ