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
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......
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´«µÝÄêÔÂÁ½¸ö²ÎÊý£¬
for(var y=2009;y<=2010;y++){
for(var m=1;m<=12;m++){
// todo : SQL ²éѯ
}
}
ÕâÑù£¬Í³¼Æ2 ......
--µ±Ç°Ê¹ÓõÄÊý¾Ý¿âÊÇ ÏµÍ³×Ô´øµÄ 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 ......