SQLÓï¾äµÄMINUS,INTERSECTºÍUNION ALL
SQLÓï¾äÖеÄÈý¸ö¹Ø¼ü×Ö:MINUS(¼õÈ¥),INTERSECT(½»¼¯)ºÍUNION ALL(²¢¼¯);
¹ØÓÚ¼¯ºÏµÄ¸ÅÄî,ÖÐѧ¶¼Ó¦¸Ãѧ¹ý,¾Í²»¶à˵ÁË.ÕâÈý¸ö¹Ø¼ü×ÖÖ÷ÒªÊǶÔÊý¾Ý¿âµÄ²éѯ½á¹û½øÐвÙ×÷,ÕýÈçÆäÖÐÎĺ¬ÒåÒ»Ñù:Á½¸ö²éѯ,MINUSÊÇ´ÓµÚÒ»¸ö²éѯ½á¹û¼õÈ¥µÚ¶þ¸ö²éѯ½á¹û,Èç¹ûÓÐÏཻ²¿·Ö¾Í¼õÈ¥Ïཻ²¿·Ö;·ñÔòºÍµÚÒ»¸ö²éѯ½á¹ûûÓÐÇø±ð. INTERSECTÊÇÁ½¸ö²éѯ½á¹ûµÄ½»¼¯,UNION ALLÊÇÁ½¸ö²éѯµÄ²¢¼¯;
ËäȻͬÑùµÄ¹¦ÄÜ¿ÉÒÔÓüòµ¥SQLÓï¾äÀ´ÊµÏÖ,µ«ÊÇÐÔÄܲî±ð·Ç³£´ó,ÓÐÈË×ö¹ýʵÑé:made_order¹²23Íò±Ê¼Ç¼£¬charge_detail¹²17Íò±Ê¼Ç¼:
SELECT order_id from made_order
¡¡¡¡MINUS
¡¡¡¡SELECT order_id from charge_detail
ºÄʱ:1.14 sec
¡¡¡¡
¡¡¡¡SELECT a.order_id from made_order a
¡¡¡¡ WHERE a.order_id NOT exists (
¡¡¡¡ SELECT order_id
¡¡¡¡ from charge_detail
¡¡¡¡ WHERE order_id = a.order_id
¡¡¡¡ )
ºÄʱ:18.19 sec
ÐÔÄÜÏà²î15.956±¶!Òò´ËÔÚÓöµ½ÕâÖÖÎÊÌâµÄʱºò,»¹ÊÇÓÃMINUS,INTERSECTºÍUNION ALLÀ´½â¾öÎÊÌâ,·ñÔòÃæ¶ÔÒµÎñÖÐËæ´¦¿É¼ûµÄÉÏ°ÙÍòÊý¾ÝÁ¿µÄ²éѯ,Êý¾Ý¿â·þÎñÆ÷»¹²»±»ÔÛÍæµÄËÀÇÌÇÌ?
PS:Ó¦ÓÃÁ½¸ö¼¯ºÏµÄÏà¼õ,ÏཻºÍÏà¼Óʱ,ÊÇÓÐÑϸñÒªÇóµÄ:1.Á½¸ö¼¯ºÏµÄ×ֶαØÐëÃ÷È·(ÓÃ*¾Í²»ÐÐ,±¨´í);2.×Ö¶ÎÀàÐͺÍ˳ÐòÏàͬ(Ãû³Æ¿ÉÒÔ²»Í¬),Èç:¼¯ºÏ1µÄ×Ö¶Î1ÊÇNUMBER,×Ö¶Î2ÊÇVARCHAR,ÄÇô¼¯ºÏ2µÄ×Ö¶Î1±ØÐëÒ²ÊÇNUMBER,×Ö¶Î2±ØÐëÊÇVARCHAR;3.²»ÄÜÅÅÐò,Èç¹ûÒª¶Ô½á¹ûÅÅÐò,¿ÉÒÔÔÚ¼¯ºÏÔËËãºó,ÍâÃæÔÙÌ×Ò»¸ö²éѯ,È»ºóÅÅÐò,ÈçÇ°ÃæµÄÀý×Ó¿ÉÒԸijÉ:
SELECT * from
(SELECT order_id from made_order
¡¡¡¡ MINUS
¡¡¡¡SELECT order_id from charge_detail)
ORDER BY ORDER_ID ASC
±¾ÎÄÀ´×ÔCSDN²©¿Í£¬×ªÔØÇë±êÃ÷³ö´¦£ºhttp://blog.csdn.net/gan690416372/archive/2009/12/15/5012397.aspx
Ïà¹ØÎĵµ£º
Êý¾Ý×Öµädict×ÜÊÇÊôÓÚOracleÓû§sysµÄ¡£
¡¡¡¡1¡¢Óû§£º
¡¡¡¡¡¡select username from dba_users;
¡¡¡¡¸Ä¿ÚÁî
¡¡¡¡¡¡alter user spgroup identified by spgtest;
¡¡¡¡2¡¢±í¿Õ¼ä£º
¡¡¡¡¡¡select * from dba_data_files;
¡¡¡¡¡¡select * from dba_tablespaces;//±í¿Õ¼ä
¡¡¡¡¡¡select tablespace_name,sum(bytes), sum(b ......
SQLÓÃselectÓï¾ä²éѯÖظ´¼Ç¼
http://hi.baidu.com/bilbit/blog/item/5ed5de16a6f6b412962b43eb.html
ÓÃselectÓï¾ä£¬²éѯÖظ´¼Ç¼
¼ÙÉ裬±íÃûΪ T1 ×Ó¶ÎΪ A,B,C
select count(*) ,A,B,C from T1
group by A,B,C having count(*) > 1
²âÊÔÊý¾Ý£º
A100 &n ......
SQLÓÃselectÓï¾ä²éѯÖظ´¼Ç¼
http://hi.baidu.com/bilbit/blog/item/5ed5de16a6f6b412962b43eb.html
ÓÃselectÓï¾ä£¬²éѯÖظ´¼Ç¼
¼ÙÉ裬±íÃûΪ T1 ×Ó¶ÎΪ A,B,C
select count(*) ,A,B,C from T1
group by A,B,C having count(*) > 1
²âÊÔÊý¾Ý£º
A100 &n ......
ÔÚÍøÉÏÕÒµÄÒ»¸ö·½·¨£¬¾ÍÒòΪһ¸öhostNameûÅäÖöԣ¬º¦µÃÎÒ»¨ÁË3¸öСʱ²Å¸ã¶¨
²âÊÔ»·¾³:
oracle·þÎñÆ÷: windowsXPϵͳ£¬ oracle9.2.0.7£¬Ö÷»úÃû£ºoracleHost
sqlserver·þÎñÆ÷:windows2003 r2ϵͳ£¬sqlserver 2000,Ö÷»úÃû£ºsqlHost
Ä¿µÄ£ºÔÚOracleÊý¾ÝÖзÃÎÊsqlserverÖеÄNorthwindÊý¾Ý¿â
1¡¢ÔÚ°²×°ORACLE9iʱ, Ò ......
localhost...²»ÄÜ´ò¿ªµ½Ö÷»úµÄÁ¬½Ó£¬ÔÚ¶Ë¿Ú 1433: Á¬½Óʧ°Ü
Æô¶¯tcp/ipÁ¬½ÓµÄ·½·¨£º
´ò¿ª
\Microsoft SQL Server 2005\ÅäÖù¤¾ß\Ŀ¼ÏµÄSQL Server Configuration
Manager£¬Ñ¡ÔñmssqlserverÐÒé,
È»ºóÓұߴ°¿ÚÓиötcp/ipÐÒ飬ÉèÖÃip/allĬÈ϶˿ÚΪ1433£¬È»ºóÆô¶¯Ëü£¬ÖØÆôsqlserver·þÎñ¡£
ÎÊÌâ½â¾ö
ÕâʱÔÚÃüÁîÐÐÊä ......