SQLÖÐexistsºÍinµÄÇø±ð
¼ÙÉèÈçÏÂÓ¦Óãº
Á½Õű헗Óû§±íTDefUser£¨userid£¬address,phone£©ºÍÏû·Ñ±íTAccConsume(userid,time,amount)£¬ÐèÒª²éÏû·Ñ³¬¹ý5000µÄÓû§¼Ç¼¡£
ÓÃexists:
select * from TDefUser
where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)
ÓÃin:
select * from TDefUser
where userid in (select userid from TAccConsume where TAccConsume.amount>5000)
ͨ³£Çé¿öϲÉÓÃexistsÒª±ÈinЧÂʸߡ£
////
exists()ºóÃæµÄ×Ó²éѯ±»³Æ×öÏà¹Ø×Ó²éѯ ËûÊDz»·µ»ØÁбíµÄÖµµÄ.Ö»ÊÇ·µ»ØÒ»¸ötrue»òfalseµÄ½á¹û(ÕâÒ²ÊÇΪʲô×Ó²éѯÀïÊÇ"select 1"µÄÔÒò£¬»»³É"select 6"ÍêÈ«Ò»Ñù£¬µ±È»Ò²¿ÉÒÔselect×ֶΣ¬µ«ÊÇÃ÷ÏÔЧÂʵÍЩ)
ÆäÔËÐз½Ê½ÊÇÏÈÔËÐÐÖ÷²éѯһ´Î£¨ÏȵóöÖ÷²éѯµÄ½á¹û£© ÔÙ¸ù¾ÝÖ÷²éѯÖеÄÿһÐÐÈ¥×Ó²éѯÀïÈ¥²éѯÓëÆä¶ÔÓ¦µÄ½á¹û.Èç¹ûÊÇtrueÔòÊä³ö,·´Ö®Ôò²»Êä³ö.
in()ºóÃæµÄ×Ó²éѯ ÊÇ·µ»Ø½á¹û¼¯µÄ,»»¾ä»°ËµÖ´ÐдÎÐòºÍexists()²»Ò»Ñù.×Ó²éѯÏȲúÉú½á¹û¼¯,È»ºóÖ÷²éѯÔÙÈ¥½á¹û¼¯ÀïÈ¥ÕÒ·ûºÏÒªÇóµÄ×Ö¶ÎÁбíÈ¥.·ûºÏÒªÇóµÄÊä³ö,·´Ö®Ôò²»Êä³ö.
£¨µÚÒ»´Î²éѯÕÒ³ö>5000µÄ½á¹û£¬µÚ¶þ´Î²éѯÅжÏuseridÊÇ·ñÔÚ½á¹ûÖУ©
////
±ÈÈçÓû§±íTDefUser£¨userid£¬address,phone£©£¬Ïû·Ñ±íTAccConsume(userid,time,amount)Êý¾ÝÈçÏ£º
Ïû·Ñ±í¾Û¼¯Ë÷ÒýÊÇuserid,time
Êý¾Ý£¨×¢ÒâÒòΪÓоۼ¯Ë÷Òý,ʵ¼Ê´æ´¢Ò²ÊÇ°´ÒÔÏ´ÎÐòµÄ£©
1 2006-1-1 200
1 2006-1-2 300
1 2006-1-2 500
1 2006-1-3 2000
1 2006-1-3 2000
1 2006-1-4 400
1 2006-1-5 500
2 2006-1-1 200
2 2006-1-2 300
2 2006-1-2 500
2 2006-1-3 2000
2 2006-1-3 6000
2 2006-1-4 400
2 2006-1-5 8000
3 2006-1-1 7000
3 2006-1-2 30000
3 2006-1-2 50000
3 2006-1-3 20000
Óï¾ä£º
select * from TDefUser
where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)
¶ÔÓÚuserid=1£¬ÐèÒªÕÒËùÓмǼ£¬²Å
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃ棬ºóÃæ ......
×÷Õߣºfreedk
Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¶þ¡¢¸ÄÉÆSQLÓï¾ä
Èý¡¢ÊµÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì
¾Û¼¯Ë÷ÒýµÄÖØÒªÐÔºÍÈçºÎÑ¡Ôñ¾Û¼¯Ë÷Òý
¡¡¡¡ÔÚÉÏÒ»½ÚµÄ±êÌâÖУ¬±ÊÕßдµÄÊÇ£ºÊµÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì¡£ÕâÊÇÒòΪÔÚ½«±¾´æ´¢¹ý³ÌÓ¦ÓÃÓÚ“°ì¹«×Ô¶¯»¯”ϵͳµÄʵ¼ùÖÐʱ£¬±ÊÕß·¢ÏÖÕ ......
execute immediate Óï·¨
ºóÃæ¸úÉÏsqlÓï¾ä¿ÉÒÔ¶¯Ì¬Ö´ÐÐsqlÓï¾ä.
ʹÓó¡¾°:
(1)¼ÓÈ붯̬±í.±ÈÈçÄãÓÐÒ»ÕűíµÄÃû×Ö²»Ã÷È·,ÊÇÐèÒªÅжϵõ½µÄ,Ôò¿ÉÒÔʹÓøÃÓï·¨À´Éú³ÉsqlÓï¾ä.
(2)¼ÓÈ붯̬×Ö¶Î.Äã¸ù¾ÝÅжÏ,×îÖÕ»á¼ÓÈëͬһÕűí,µ«ÊÇÕâÕűí¸ù¾Ý²»Í¬µÄÅжÏ×ֶλáÓÐËù²»Í¬,´ËʱÈç¹ûÖ±½ÓдsqlÊǼӲ»½øÈ¥µÄ,±ØÐëʹÓö¯Ì¬sqlÓï¾ä,ÈÃo ......
ÒÔÏÂÊǾ²âÑéÄÜÕýÈ·ÔËÐеÄ:
create table t_emp_log212
(
who varchar2(10) not null,
action varchar2(10) not null,
actime date
);
create or replace trigger tri_emp_insert
before insert
on t_emp
begin
insert into t_emp_log(who,action,actime)values(user,'insert',sysdate);
end ......