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

sql²éѯÓï¾äÖÐ inºÍ existsµÄÇø±ðÓëÐÔÄܱȽÏ

¶ÔInºÍexistsµÄÐÔÄܽøÐбȽϣ¬Ê×ÏÈÒªÖªµÀËüÃÇÁ½ÕßµÄÇø±ð¡£
in: È·¶¨¸ø¶¨µÄÖµÊÇ·ñÓë×Ó²éѯÖеÄÖµ»òÁбíÖеÄÖµÏàÆ¥Åä¡£
exists: Ö¸¶¨Ò»¸ö×Ó²éѯ£¬¼ì²âÐÐÊÇ·ñ´æÔÚ¡£
¿É·ÖÎöËüÃǵIJéѯÓï¾äÀ´µÃ³öÕæÊµµÄ²î±ð:
in
±ÈÈçSelect * from t1 where x in ( select y from t2 )
Ö´ÐеĹý³ÌÏ൱ÓÚ:
select *
  from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
exists
±ÈÈçselect * from t1 where exists ( select t2.z from t2 where y = x )
Ö´ÐеĹý³ÌÏ൱ÓÚ:
for t in ( select * from t1 )
   loop
      if ( exists ( select t2.z from t2 where y = t.x )
      then
         OUTPUT THE RECORD
      end if
end loop
´ÓÉÏÃæµÄÓï¾ä¿ÉÒÔ¿´³ö£¬¶Ôt1±í²»¿É±ÜÃâµÄ½øÐÐÁËÒ»´ÎÈ«ÅÌɨÃè
inºÍexists
in ÊǰÑÍâ±íºÍÄÚ±í×÷hash Á¬½Ó£¬¶øexistsÊǶÔÍâ±í×÷loopÑ­»·£¬Ã¿´ÎloopÑ­»·ÔÙ¶ÔÄÚ±í½øÐвéѯ¡£
Ò»Ö±ÒÔÀ´ÈÏΪexists±ÈinЧÂʸߵÄ˵·¨ÊDz»×¼È·µÄ¡£
Èç¹û²éѯµÄÁ½¸ö±í´óСÏ൱£¬ÄÇôÓÃinºÍexists²î±ð²»´ó¡£
Èç¹ûÁ½¸ö±íÖÐÒ»¸ö½ÏС£¬Ò»¸öÊÇ´ó±í£¬Ôò×Ó²éѯ±í´óµÄÓÃexists£¬×Ó²éѯ±íСµÄÓÃin£º
ÀýÈ磺±íA£¨Ð¡±í£©£¬±íB£¨´ó±í£©
1£º
select * from A where cc in (select cc from B)
ЧÂʵͣ¬Óõ½ÁËA±íÉÏccÁеÄË÷Òý£»
select * from A where exists(select cc from B where cc=A.cc)
ЧÂʸߣ¬Óõ½ÁËB±íÉÏccÁеÄË÷Òý¡£
Ïà·´µÄ
2£º
select * from B where cc in (select cc from A)
ЧÂʸߣ¬Óõ½ÁËB±íÉÏccÁеÄË÷Òý£»
select * from B where exists(select cc from A where cc=B.cc)
ЧÂʵͣ¬Óõ½ÁËA±íÉÏccÁеÄË÷Òý¡£
not in ºÍnot exists
Èç¹û²éѯÓï¾äʹÓÃÁËnot in ÄÇôÄÚÍâ±í¶¼½øÐÐÈ«±íɨÃ裬ûÓÐÓõ½Ë÷Òý£»
¶ønot extsts µÄ×Ó²éѯÒÀÈ»ÄÜÓõ½±íÉϵÄË÷Òý¡£
ËùÒÔÎÞÂÛÄǸö±í´ó£¬ÓÃnot exists¶¼±Ènot inÒª¿ì¡£ 
 


Ïà¹ØÎĵµ£º

MS SQL SERVER º£Á¿Êý¾ÝµÄ²éѯÓÅ»¯¼°·ÖÒ³Ëã·¨


̽ÌÖÈçºÎÔÚÓÐ×Å1000ÍòÌõÊý¾ÝµÄMS SQL SERVERÊý¾Ý¿âÖÐʵÏÖ¿ìËÙµÄÊý¾ÝÌáÈ¡ºÍÊý¾Ý·ÖÒ³¡£ÒÔÏ´úÂë˵Ã÷ÁËÎÒÃÇʵÀýÖÐÊý¾Ý¿âµÄ“ºìÍ·Îļþ”Ò»±íµÄ²¿·ÖÊý¾Ý½á¹¹£º
CREATE TABLE [dbo].[TGongwen] (    --TGongwenÊǺìÍ·Îļþ±íÃû
   [Gid] [int]&nb ......

SQLÐÐתÁÐ ÁÐתÐÐ


--ÐÐÁл¥×ª
/****************************************************************************************************************************************************** 
ÒÔѧÉú³É¼¨ÎªÀý×Ó£¬±È½ÏÐÎÏóÒ×¶® ÕûÀíÈË£ºÖйú·ç
(Roy) 
ÈÕÆÚ
:2008.06.06 *************************************** ......

SQL²éѯÅóÓѹØÏµÈ¦Ó¦ÓÃʵÀý

Ô­Ìù£ºhttp://topic.csdn.net/u/20100412/11/a4ea520e-7dd0-44d2-98bb-9f62f0ed6160.html?21233
--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-14 06:02:36
--  Version:Microsoft SQL Server 2008 (RTM) - 1 ......

»ñµÃÊý¾Ý¿âÃû³Æ///////²éѯÊý¾Ý¿âÀïËùÓбíµÄsqlÓï¾ä£¡

select   name   as   ¿âÃû   from   master..sysdatabases   where   dbid>=7 »ñµÃÊý¾Ý¿âÃû³Æ
select * from sysobjects where xtype='u' or xtype='v' ²éѯÊý¾Ý¿âÀïËùÓбíµÄsqlÓï¾ä£¡
select name from sysobjects where type='u' ²éѯһ¸ö¿ ......

ʹÓÃPL/SQL Developer¹¤¾ßµ¼ÈëexcelºÍµ¼³öexcel

µ¼³ö:
1.Ö´ÐÐ
select Óï¾ä²éѯ³öÐèÒªµ¼³öµÄÊý¾Ý.
2.ÔÚÊý¾ÝÁбíÖÐÓÒ¼ü,Ñ¡Ôñsave
results.±£´æÎª.csvÎļþ,È»ºóÒÑexcel·½Ê½´ò¿ª¾ÍOKÁË.¿ÉÒÔÁí´æÎªxsl¡£ÐèҪעÒâµÄÊÇÈç¹ûÁÐÄÚÈÝÊÇ´¿Êý×Ö
µÄ»°£¬µ¼³öʱ»á×Ô¶¯°ÑÇ°ÃæµÄ0È¥µô£¬ÏëÒªµ¼³öÍêÕûµÄÁÐÄÚÈÝ£¬×îºÃÕâÁеÄÄÚÈÝÆ´Ò»¸ö×Ö·ûÈ磺select  ‘#’||Column from ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ