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

sqlÀïµÄexistsÓëin¡¢not existsÓënot inµÄÇø±ð

ϵͳҪÇó½øÐÐSQLÓÅ»¯£¬¶ÔЧÂʱȽϵ͵ÄSQL½øÐÐÓÅ»¯£¬Ê¹ÆäÔËÐÐЧÂʸü¸ß£¬ÆäÖÐÒªÇó¶ÔSQLÖеIJ¿·Öin/not inÐÞ¸ÄΪexists/not exists
Ð޸ķ½·¨ÈçÏ£º
inµÄSQLÓï¾ä
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
from tab_oa_pub WHERE is_check=1 and
category_id in (select id from tab_oa_pub_cate where no='1')
order by begintime desc
ÐÞ¸ÄΪexistsµÄSQLÓï¾ä
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
from tab_oa_pub WHERE is_check=1 and
exists (select id from tab_oa_pub_cate where tab_oa_pub.category_id=convert(int,no) and no='1')
order by begintime desc
·ÖÎöÒ»ÏÂexistsÕæµÄ¾Í±ÈinµÄЧÂʸßÂð£¿
    ÎÒÃÇÏÈÌÖÂÛINºÍEXISTS¡£
    select * from t1 where x in ( select y from t2 )
    ÊÂʵÉÏ¿ÉÒÔÀí½âΪ£º
    select *
      from t1, ( select distinct y from t2 ) t2
     where t1.x = t2.y;
    ——Èç¹ûÄãÓÐÒ»¶¨µÄSQLÓÅ»¯¾­Ñ飬´ÓÕâ¾äºÜ×ÔÈ»µÄ¿ÉÒÔÏëµ½t2¾ø¶Ô²»ÄÜÊǸö´ó±í£¬ÒòΪÐèÒª¶Ôt2½øÐÐÈ«±íµÄ“ΨһÅÅÐò”£¬Èç¹ût2ºÜ´óÕâ¸öÅÅÐòµÄÐÔÄÜÊDz»¿ÉÈÌÊܵġ£µ«ÊÇt1¿ÉÒԺܴó£¬ÎªÊ²Ã´ÄØ£¿×îͨË×µÄÀí½â¾ÍÊÇÒòΪt1.x=t2.y¿ÉÒÔ×ßË÷Òý¡£µ«Õâ²¢²»ÊÇÒ»¸öºÜºÃµÄ½âÊÍ¡£ÊÔÏ룬Èç¹ût1.xºÍt2.y ¶¼ÓÐË÷Òý£¬ÎÒÃÇÖªµÀË÷ÒýÊÇÖÖÓÐÐòµÄ½á¹¹£¬Òò´Ët1ºÍt2Ö®¼ä×î¼ÑµÄ·½°¸ÊÇ×ßmerge join¡£ÁíÍ⣬Èç¹ût2.yÉÏÓÐË÷Òý£¬¶Ôt2µÄÅÅÐòÐÔÄÜÒ²ÓкܴóÌá¸ß¡£
    select * from t1 where exists ( select null from t2 where y = x )
    ¿ÉÒÔÀí½âΪ£º
    for x in ( select * from t1 )
    loop
       if ( exists ( select null from t2 where y = x.x )
       then
          OUTPUT THE RECORD!
       end if
    end loop
    ——Õâ¸ö¸üÈÝÒ×Àí½â£¬t1ÓÀÔ¶ÊǸö±íɨÃ裡Òò´Ët1¾ø¶Ô²»ÄÜÊǸö´ó±í£¬¶øt


Ïà¹ØÎĵµ£º

º½¿Õ¹«Ë¾¹ÜÀíϵͳ(VC++ ÓëSQL 2005)

ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
      ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......

SQL Server º¯Êý´óÈ«

SQL Server×Ö·û´®´¦Àíº¯Êý´óÈ«
 selectÓï¾äÖÐÖ»ÄÜʹÓÃsqlº¯Êý¶Ô×ֶνøÐвÙ×÷£¨Á´½Ósql server£©£¬
select ×Ö¶Î1 from ±í1 where ×Ö¶Î1.IndexOf("ÔÆ")=1;
ÕâÌõÓï¾ä²»¶ÔµÄÔ­ÒòÊÇindexof£¨£©º¯Êý²»ÊÇsqlº¯Êý£¬¸Ä³Ésql¶ÔÓ¦µÄº¯Êý¾Í¿ÉÒÔÁË¡£
left£¨£©ÊÇsqlº¯Êý¡£
select ×Ö¶Î1 from ±í1 where charindex£¨'Ô ......

SQL °´Ô²éѯ

ÏîÄ¿ÖÐÐèÒª¸ù¾Ý¿Î¼þÃû³Æ°´ÔÂͳ¼Æ³ö·ÃÎʵÄÇé¿ö£¬µÚÒ»´ÎÎÒ²ÉÓÃÁË×îÍÁµÄÒ»ÖÖ°ì·¨£¬Ê¹ÓÃÑ­»·£¬¸øsql´«µÝÄêÔÂÁ½¸ö²ÎÊý£¬
for(var y=2009;y<=2010;y++){
    for(var m=1;m<=12;m++){
          // todo : SQL ²éѯ
    }
}
ÕâÑù£¬Í³¼Æ2 ......

Ò»¶ÎsqlÓï¾äµÄ·ÖÎö

--µ±Ç°Ê¹ÓõÄÊý¾Ý¿âÊÇ ÏµÍ³×Ô´øµÄ master
create database temp1
go --´Ë´¦²»¼ÓgoµÄ»°ÏÂÃæuse temp1 »á±¨´í£ºÕÒ²»µ½´æ´¢¹ý³Ì 'temp1'¡£
use temp1
set xact_abort on
begin tran
create table [order]( --orderÊǹؼü×Ö±ØÐëÓÃ[ ]£»
id int
)
create table fOrder(
id int
)
-- ÏÂÃæµÄ²Ù×÷Ö÷ÒªÊÇΪÁËʵÏÖfO ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ