Oracle¡¢SQL Server¡¢AccessÊý¾Ý¿â¸ßЧ¹û·ÖÒ³¼¼ÇÉ
ÔÚ³ÌÐòµÄ¿ª·¢¹ý³ÌÖУ¬´¦Àí·ÖÒ³ÊÇ´ó¼Ò½Ó´¥±È½ÏƵ·±µÄʼþ£¬ÒòΪÏÖÔÚÈí¼þ»ù±¾É϶¼ÊÇÓëÊý¾Ý¿â½øÐйҵöµÄ¡£µ«Ð§ÂÊÓÖÊÇÎÒÃÇËù×·ÇóµÄ£¬Èç¹ûÊÇÏñÔÀ´ÄÇÑù°ÑËùÓÐÂú×ãÌõ¼þµÄ¼Ç¼ȫ²¿¶¼Ñ¡Ôñ³öÀ´£¬ÔÙÈ¥½øÐзÖÒ³´¦Àí£¬ÄÇô¾Í»á¶à¶àµÄÀ˷ѵôÐí¶àµÄϵͳ´¦Àíʱ¼ä¡£ÎªÁËÄܹ»°ÑЧÂÊÌá¸ß£¬ËùÒÔÏÖÔÚÎÒÃǾÍֻѡÔñÎÒÃÇÐèÒªµÄÊý¾Ý£¬¼õÉÙÊý¾Ý¿âµÄ´¦Àíʱ¼ä£¬ÒÔϾÍÊdz£ÓÃSQL·ÖÒ³´¦Àí£º
¡¡¡¡1¡¢SQL Server¡¢AccessÊý¾Ý¿â
¡¡¡¡Õⶼ΢ÈíµÄÊý¾Ý¿â£¬¶¼ÊÇÒ»¼ÒÈË£¬»ù±¾µÄ²Ù×÷¶¼ÊDz¶à£¬³£²ÉÓÃÈçÏ·ÖÒ³Óï¾ä£º
¡¡¡¡PAGESIZE£ºÃ¿Ò³ÏÔʾµÄ¼Ç¼Êý
¡¡¡¡CURRENTPAGE£ºµ±Ç°Ò³ºÅ
¡¡¡¡Êý¾Ý±íµÄÃû×ÖÊÇ£ºcomponents
¡¡¡¡Ë÷ÒýÖ÷¼ü×ÖÊÇ£ºid
ÒÔÏÂÊÇÒýÓÃƬ¶Î£º
¡¡¡¡select top PAGESIZE * from components where id not in
¡¡¡¡(select top (PAGESIZE*(CURRENTPAGE-1))
¡¡¡¡id from components order by id)order by id
¡¡¡¡ÈçÏÂÁУº
ÒÔÏÂÊÇÒýÓÃƬ¶Î£º
¡¡¡¡select top 10 * from components where id not in
¡¡¡¡(select top 10*10 id from components order by id)
¡¡¡¡order by id
¡¡¡¡´Ó101Ìõ¼Ç¼¿ªÊ¼Ñ¡Ôñ£¬Ö»Ñ¡ÔñÇ°ÃæµÄ10Ìõ¼Ç¼
¡¡¡¡2¡¢OracleÊý¾Ý¿â
¡¡¡¡ÒòΪOracleÊý¾Ý¿âûÓÐTop¹Ø¼ü×Ö£¬ËùÒÔÕâÀï¾Í²»Äܹ»Ïñ΢ÈíµÄÊý¾Ý¾ÝÄÇÑù²Ù×÷£¬ÕâÀïÓÐÁ½ÖÖ·½·¨£º
¡¡¡¡(1)¡¢Ò»ÖÖÊÇÀûÓÃÏà·´µÄ¡£
¡¡¡¡PAGESIZE£ºÃ¿Ò³ÏÔʾµÄ¼Ç¼Êý
¡¡¡¡CURRENTPAGE£ºµ±Ç°Ò³ºÅ
¡¡¡¡Êý¾Ý±íµÄÃû×ÖÊÇ£ºcomponents
¡¡¡¡Ë÷ÒýÖ÷¼ü×ÖÊÇ£ºid
ÒÔÏÂÊÇÒýÓÃƬ¶Î£º
¡¡¡¡select * from components where id not
¡¡¡¡in(select id from components where
¡¡¡¡rownum<=(PAGESIZE*(CURRENTPAGE-1)))
¡¡¡¡and rownum<=PAGESIZE order by id;
¡¡¡¡ÈçÏÂÀý£º
ÒÔÏÂÊÇÒýÓÃƬ¶Î£º
¡¡¡¡select * from components where id not in
¡¡¡¡(select id from components where rownum<=100)
¡¡¡¡and rownum<=10 order by id;
¡¡¡¡´Ó101µ½¼Ç¼¿ªÊ¼Ñ¡Ôñ£¬Ñ¡ÔñÇ°Ãæ10Ìõ¡£
¡¡¡¡(2)¡¢Ê¹ÓÃminus£¬¼´ÖÐÎĵÄÒâ˼¾ÍÊǼõÈ¥¡£
ÒÔÏÂÊÇÒýÓÃƬ¶Î£º
¡¡¡¡
Ïà¹ØÎĵµ£º
Ó¦¸ö±¨±íÒªÇó×ö¸ö
ʱ¼ä Ë«É«Çò£¬ ʤ¸º²Ê¡£¡£¡£¡£¡£
200905 ÏúÁ¿ ÏúÁ¿
200906 ÏúÁ¿ ÏúÁ¿
200907 &n ......
´æ´¢¹ý³ÌÔÚ·þÎñÆ÷¶ËÔçÒѱà¼Ö´ÐйýµÄ´úÂë¡£Óû§Òª×öµÄÖ»Êǵ÷ÓúͽÓÊÕ´æ´¢¹ý·µ»ØµÄ½á¹û¡£ËùÒÔµ÷Óô洢¹ý³Ì±ÈÆÕͨµÄÓòéѯÓï¾ä·µ»ØÖµÒª¿ìµÃ¶à,´æ´¢¹ý³ÌµÄÖ´ÐÐËٶȸü¿ì,´æ ´¢¹ý³ÌÊDZ£´æÆðÀ´µÄ¿ÉÒÔ½ÓÊܺͷµ»ØÓû§ÌṩµÄ²ÎÊýµÄ Transact-SQL Óï¾äµÄ¼¯ºÏ¡£¿ÉÒÔ´´½¨Ò»¸ö¹ý³Ì¹©ÓÀ¾ÃʹÓ㬻òÔÚÒ»¸ö»á»°ÖÐÁÙʱʹÓ㨾ֲ¿ÁÙʱ¹ý ......
oracle11g¾ßÓÐ×Ô¶¯µÄ±íѹËõ¹¦ÄÜ£¬ µ«µ±insertÓï¾äδָ¶¨¾ßÌåµÄÁÐÃûʱ£¬ »áʹÓÃ×Ô¶¯±íѹËõ¹¦ÄÜʧЧ¡£(Èç¸ÃÓï¾ä»áʹµÃ±ít_test²»ÄÜ×Ô¶¯Ñ¹Ëõ: insert into t_test select * from t_test2)
ÁíÍâʹÓÃһЩÍⲿ¹¤¾ß½øÐÐÊý¾Ý×°ÔØ(sqlload)£¬Ò²ÓпÉÄÜʹµÃ±í²»ÄÜ×Ô¶¯Ñ¹Ëõ£¬´ËʱÐèÒªÓÃÒÔÏÂÓï¾ä£¬ÒÔÖØзÖÎö±í£¬·ÖÎöÍê³ÉÖ®ºó£¬¸Ã±í¼´»á ......
ÎÄÕÂÌáµ½£¬ÓÃCache hit ratioµÄ·½·¨À´¼ì²éORACLEÐÔÄÜÎÊÌâ¹ýʱÁË£¬ÀïÃæÓоä·Ç³£ÐÎÏóµÄ»°£ºÕâÎÞÒìÓÚÒ»¸öÒ½ÉúÖ»ÖªµÀ¸ù¾ÝѪѹµÄÀ´ÖÎÁƲ¡ÈË£¬¶ø²¡ÈËÓÉÓÚÌÛÍ´£¬Éñ¾ÐË·Ü£¬ÑªÑ¹ÔÚÒ»¸öºÏÀíµÄ·¶Î§ÄÚ£¬Ò½ÉúÈ´¸æËß»¼Õߣ¬Äãû²¡£¬µÈÄãѪѹµÍµÄʱºòÔÙÀ´¡£Í¬ÑùµÄ£¬ÎÒÃDz»Äܽö½ö¸ù¾ÝCache hit ratioÀ´ÅжÏORACLEÊ ......
Group functions
SELECT [column,] group_function(column) ... from table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];
e.g.:
SELECT department_id, job_id, SUM(salary), COUNT(employee_id) from employees GROUP BY department_id, job_id ;
SELECT [column,] group_function(column).. ......