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í´ïʽ±¾Éí£¬¶ø²»
Ïà¹ØÎĵµ£º
·ÖÒ³²éѯ¸ñʽ£º
SELECT * from
(
SELECT A.*, ROWNUM RN
from (SELECT * from TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
ÆäÖÐ×îÄÚ²ãµÄ²éѯSELECT * from TABLE_NAME±íʾ²»½øÐзҳµÄÔʼ²éѯÓï¾ä¡£ROWNUM <= 40ºÍRN >= 21¿ØÖÆ·ÖÒ³²éѯµÄÿҳµÄ·¶Î§¡£
ÉÏÃæ¸ø³öµÄÕâ¸ö·ÖÒ³²éѯÓï¾ä£¬ÔÚ´ó¶à ......
ÎÒÔÚ¹¤×÷ÖÐÔÚOracleÖжà±í²éѯÔÙ°´Ê±¼äµ¹Ðòʱ£¬ÓÃorder by ±íÃû.datatime desc ×ÜÊÇʵÏÖ²»ÁË¡£
²»¹ý×îºóÎÊÑî¾é×îºóºÍÎÒ˵¿ÉÒÔ°ÑÎÒÃDzéѯµÄ×ܸösqlÓï¾ä×öΪһ¸ö±íÀ´ÅÅÐò£º
select rownum,aa.* from (.... order by ±íÃû.datatime desc ) aa ......
create database link user_auth
¡¡¡¡ connect to user_auth identified by user_auth
¡¡¡¡ using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.236)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=develop1)))'; ......
Àí½âoracleÖÐÁ¬½ÓºÍ»á»° 1. ¸ÅÄͬ£º¸ÅÄͬ£º
Á¬½ÓÊÇÖ¸ÎïÀíµÄÍøÂçÁ¬½Ó¡£
ÔÚÒѽ¨Á¢µÄÁ¬½ÓÉÏ£¬½¨Á¢¿Í»§¶ËÓëoracleµÄ»á»°£¬ÒÔºó¿Í»§¶ËÓëoracleµÄ½»»¥¶¼ÔÚÒ»¸ö»á»°»·¾³ÖнøÐС£
2. ¹ØÏµÊǶà¶Ô¶à£º
Ò»¸öÁ¬½ÓÉÏ¿ÉÒÔ½¨Á¢0¸ö£¬1¸ö£¬2¸ö£¬¶à¸ö»á»°¡£
OracleÔÊÐí´æÔÚÕâÑùµÄ»á»°£¬¾ÍÊÇʧȥÁËÎïÀíÁ¬½ÓµÄ»á»°¡£
3. ¸ÅÄîÓ¦Ó㺸 ......
ÔÚ³õѧOracleʱ£¬Ð´ÁËÒ»¸ö´æ´¢¹ý³Ì£¬Ãû³ÆÊÇ£ºPROC_GET_BILL£¬Èý¸ö²ÎÊý£¬µÚ1£¬3ÊÇin²ÎÊý£¬µÚ2ÊÇout²ÎÊý£¬Ð´ÍêÖ®ºó£¬Ïë²âһϣ¬½á¹û·¢ÏÖÍøÉÏÓжàÖÖ·½Ê½£¨ÆäÖØÒªÊÇÏÂÃæÕâÁ½ÖÖ£¬Ö»ÊÇд·¨²»Í¬¶øÒÑ£©£¬¸Õ¿ªÊ¼°ÑÁ½ÖÖ±äÁ¿¶¨Ò巽ʽ¸ã´íÁË£¬Ò»Ö±Ö´Ðв»¹ý£¬¾ÂýÂý³¢ÊÔ£¬µÃµ½ÁËÏÂÃæÁ½ÖÖд·¨£¬Ï£ÍûÏñÎÒÕâÑù³õѧÕßÉÙ×ßÍä·£¬Ö±½Ó¸ãÇåÁ½ÖÖ· ......