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
Ïà¹ØÎĵµ£º
£¨1£©Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò£¨Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ£©
oracleµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒÖÁ×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í£¨»ù´¡±í£©½«×îÏȱ»´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, ÄǾÍÐèҪѡÔñ½»²æ±í(intersection table)×÷Ϊ»ù´ ......
SQL SERVER 2005 ÖеÄÈÕÆÚʱ¼äÀàÐÍ
Ò»£® Êý¾ÝÀàÐÍ
MS SQL Server 2005 ÓÐ datetime ºÍ smalldatetime Á½ÖÖ¸ñʽµÄÈÕÆÚʱ¼äÊý¾ÝÀàÐÍ¡£×¢Ò⣺ûÓе¥¶ÀµÄÈÕÆÚ»òʱ¼äÀàÐÍ¡£
datetime
Êý¾Ý¿âÄÚ²¿ÓÃÁ½¸ö 4 ×Ö½ÚµÄÕûÊý´æ´¢ datetime Êý¾ÝÀàÐ͵ÄÖµ¡£µÚÒ»¸ö 4 ×Ö½Ú´æ´¢»ù´¡ÈÕÆÚ£¨¼´ 1900-1-1, base ......
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varch ......
SQL> select * from t1;
ID AGE
---------- ----------
1 20
2&nbs ......
create procedure DeleteWareHouse_StoreArea_SummaryByPUR
(@po_no nvarchar(100))
as
begin
declare @cacheTable table(wh_id int);--ÉùÃ÷Ò»¸ötableÀàÐ͵ıäÁ¿
insert @cacheTable select wh_id from aps_inventory_store_area where description=@po_no--Ïò±äÁ¿@cacheTableÖÐÌí¼Ó½á¹û¼¯
--select * from @cac ......