Oracle Group By Ó÷¨Ö® —— Having
¿Í»§ÐèÇó·ÖÎö£º
¡¡¡¡±ÊÕß×î½ü½Óµ½Ò»¼Ò¿Í»§µÄÒ»¸öÐèÇó¡£ËûÃDz¿ÊðÁËÒ»¸öERPϵͳ£¬ÏÖÔÚ²ÉÓõľÍÊÇOracleÊý¾Ý¿â¡£ÏÖÔÚÓÉÓÚÆóҵͳ¼Æ·ÖÎöµÄÐèÒª£¬ÒªÊµÏÖÈçϵÄÐèÇó¡£
¡¡¡¡1¡¢°´Ô·ÝÀ´Í³¼Æ2009ÄêµÚÒ»¼¾¶Èÿ¸ö¹©Ó¦É̵IJɹº½ð¶î¡£Ò²¾ÍÊÇ˵£¬ÔÚ±¨±íÖÐÒªÄܹ»ÏÔʾ³ö2009Äê1Ô·ݡ¢2Ô·ݡ¢3Ô·ݹ©Ó¦É̵IJɹº½ð¶îºÏ¼Æ£¬²»ÐèÒªÃ÷ϸ¡£
¡¡¡¡2¡¢ÏÔʾµÄ½á¹û°´Äê¶È¡¢Ô·ݡ¢¹©Ó¦ÉÌÃû×Ö½øÐÐÅÅÐò¡£
¡¡¡¡
¡¡¡¡×ۺϿͻ§µÄÒªÇó£¬Ò²¾ÍÊÇ˵ҪʵÏÖÈçÉÏÕâÕű¨±í¡£
¡¡¡¡PL/SQLÓï¾ä½âÎö£º
¡¡¡¡select extract(YEAR from t.dateordered) AS Äê¶È,extract(MONTH from t.dateordered) as Ô·Ý,
¡¡¡¡p.name as ¹©Ó¦ÉÌÃû×Ö,sum(t.linenetamt) ºÏ¼Æ
¡¡¡¡from c_orderline2 t
¡¡¡¡left join c_bpartner p on p.c_bpartner_id=t.c_bpartner_id
¡¡¡¡group by extract(YEAR from t.dateordered),extract(MONTH from t.dateordered),p.name
¡¡¡¡having extract(YEAR from t.dateordered)=2009 and extract(MONTH from t.dateordered) in (1,2,3)
¡¡¡¡order by p.name;
¡¡¡¡Í¨¹ýÒÔÉÏÓï¾ä¾Í¿ÉÒÔʵÏÖÆóÒµÈçÉϵÄÐèÇó¡£ÔÚÕâ¸öÐèÇóÖУ¬±ÊÕßÖ÷ÒªÓùýGroup ByÓï¾äÓëHavingÓï¾äÀ´ÊµÏÖ¡£ÕâÁ½¸öÊÇOralceÊý¾Ý¿âÖÐPL/SQLÓïÑÔÖÐÁ½¸öºÜÖØÒªµÄ·Ö×éÓï¾ä¡£ÀûÓÃÕâ¸öÁ½¸öÓï¾ä¿ÉÒÔʵÏÖһЩ¸´ÔÓµÄͳ¼Æ¹¦ÄÜ¡£¶ÔÓÚGroup ByÓëHavingÓï¾äµÄһЩ»ù±¾Ó÷¨£¬±ÊÕßÔÚÕâÀï²»×ö¹ý¶àÃèÊö¡£±ÊÕßÕâÀïÏë˵µÄÊÇ£¬ÔÚʹÓÃÕâÁ½¸öÓï¾ä½øÐÐÊý¾Ýͳ¼ÆÊ±ÐèҪעÒâµÄµØ·½¡£ÔÚOracleÊý¾Ý¿âϵͳÖУ¬¶ÔÓÚÕâÁ½¸öͳ¼Æ×Ó¾ä×öÁ˱ȽÏÑϸñµÄʹÓÃÏÞÖÆ¡£Êý¾Ý¿â¹ÜÀíÔ±±ØÐë¶ÔÕâЩʹÓÃÏÐÖÃÃú¼ÇÔÚÐÄ£¬·ñÔòµÄ»°ºÜÈÝÒ×ÔÚͳ¼ÆµÄ¹ý³ÌÖÐÓöµ½´íÎó¡£¾ßÌåÀ´Ëµ£¬ÓÐÈçϼ¸¸öʹÓÃÏÞÖÆ¡£
¡¡¡¡1¡¢Èç¹ûÑ¡ÔñÁбíÖаüº¬ÓÐÁС¢±í´ïʽ»òÕß·Ö×麯Êý£¬ÄÇôÕâЩÁлòÕß±í´ïʽ±ØÐë³öÏÖÔÚGroup By×Ó¾äÖУ¬·ñÔòÊý¾Ý¿â»áÌáʾÏà¹ØµÄ´íÎóÐÅÏ¢¡£·Ö×麯Êý²»ÓóöÏÖÔÚGroup By×Ó¾äÖС£ÈçÉÏÃæÕâ¸öÀý×Ó£¬ÓÉÓÚÔÚÊý¾Ý¿â»ù´¡±íÖд洢µÄÊÇ϶©µ¥µÄÈÕÆÚ£¬Èç2009Äê4ÔÂ15ÈÕ¡£Ò²¾ÍÊÇ˵£¬ÄêÔÂÈÕÊÇ´æ´¢ÔÚͬһ¸ö×Ö¶ÎÖеġ£µ«ÊÇÔÚͳ¼ÆµÄʱºò£¬ÐèҪͳ¼Æ2009Äê1Ô¡¢2Ô¡¢3ԵũӦÉ̲ɹº½ð¶î¡£Îª´Ë´Ëʱ±ÊÕßÏÈÀûÓÃExtractº¯Êý´ÓÒ»¸öÈÕÆÚÊý¾ÝÖгéÈ¡¾ßÌåµÄÄê¡¢ÔÂÐÅÏ¢¡£Õâ¸öÊÇOracleÊý¾Ý¿âÖÐÒ»¸öºÜÓÐÓõÄÈÕÆÚº¯Êý¡£ÒªÊÇûÓÐÕâ¸öº¯ÊýµÄ»°£¬±ÊÕß»¹ÐèҪͨ¹ý×Ö·û´®µÈ´¦Àíº¯ÊýÀ´½ØÈ¡ÄêÔµÈÐÅÏ¢¡£ÓÉÓÚExtractÊÇÒ»¸ö´øº¯ÊýµÄ±í´ïʽ£¬Îª´ËÆä±ØÐë³öÏÖÔÚGroup By×Ó¾äÖС£¶øÇÒ×¢Ò⣬±ÊÕß´Ëʱ²ÉÓõÄÊDZí´ïʽ±¾Éí£¬¶ø²»
Ïà¹ØÎĵµ£º
Ò»¡¢ ³£ÓÃÈÕÆÚÊý¾Ý¸ñʽ
1.Y»òYY»òYYY ÄêµÄ×îºóһ룬Á½Î»»òÈýλ
SQL> Select to_char(sysdate,'Y') from dual;
TO_CHAR(SYSDATE,'Y')
--------------------
7
SQL> Select to_char(sysdate,'YY') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------
07
SQL> Select to_char(sysdate,'YYY') from ......
˵Ã÷:ÏÂÎÄÖеÄһЩ˵Ã÷ºÍʾÀý´úÂëÕª×ÔCSDN,Ë¡²»Ò»Ò»Ö¸Ã÷³ö´¦,ÔÚ´ËÒ»²¢¶ÔÏà¹Ø×÷Õß±íʾ¸Ðл!
¡¡¡¡1 Óï·¨
¡¡¡¡ÔÚOracleÖУ¬¿ÉÒÔ´´½¨ÒÔÏÂÁ½ÖÖÁÙʱ±í£º
¡¡¡¡1) »á»°ÌØÓеÄÁÙʱ±í
¡¡¡¡CREATE GLOBAL TEMPORARY ( )
¡¡¡¡ON COMMIT PRESERVE ROWS£»
¡¡¡¡2) ÊÂÎñÌØÓеÄÁÙʱ±í
¡¡¡¡CREATE GLOBAL TEMPORARY ( )
¡¡¡¡O ......
³éÏóÊý¾ÝÀàÐÍ
1¡¢´´½¨ÀàÐÍ
--µØÖ·ÀàÐÍ
CREATE OR REPLACE TYPE AddressType AS OBJECT
(
Country varchar2(15),
City varchar2(20),
Street varchar2(30)
);
2¡¢ÀàÐÍǶÌ×
--´´½¨»ùÓÚǰһ¸öÀàÐ͵ÄеijéÏóÊý¾ÝÀàÐÍ£º¾ÞÐÇÀàÐÍ
CREATE OR REPLACE TYPE SuperStarType AS OBJECT
(
StarName varchar2(30),
Addres ......
OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃüÁî¿ÉÒÔ°ÑdmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓÃÀ´ÕýʽʹÓá£
Ö´Ðл·¾³£º¿ÉÒÔÔÚSQLPLUS.EXE»òÕßDOS£¨ÃüÁîÐУ ......
create table a(
id number primary key,
name varchar2(20)
);
create table b(
id number,
name varchar2(20),
aid number references a(id)
);
insert into a values(1,'1');
insert into b values(1,'aaa',1);
Èç¹û create view vi_new as select * from a
ÔÚÊÓͼvi_new ÉÏ×öÈκΠdml²Ù×÷¶¼»áÐÞ¸Ä ¶ÔÓ¦µÄ ......