[Sql]EXCEPT ºÍ INTERSECT¹Ø¼ü×Ö
[Sql]EXCEPT ºÍ INTERSECT¹Ø¼ü×Ö
http://www.cnblogs.com/treeyh/archive/2008/07/01/1232845.html
EXCEPT
´Ó EXCEPT ²Ù×÷Êý×ó±ßµÄ²éѯÖзµ»ØÓұߵIJéѯδ·µ»ØµÄËùÓзÇÖظ´Öµ¡£
INTERSECT
·µ»Ø INTERSECT ²Ù×÷Êý×óÓÒÁ½±ßµÄÁ½¸ö²éѯ¾ù·µ»ØµÄËùÓзÇÖظ´Öµ¡£
A. ʹÓà EXCEPT
ÔÚʾÀýÖÐʹÓà TableA ºÍ TableB ÖеÄÊý¾Ý¡£
TableA (col1 int)
TableB (col1 int)
NULL
NULL
NULL
1
NULL
3
1
4
2
4
2
2
3
4
4
ÒÔϲéѯ´Ó EXCEPT
²Ù×÷Êý×ó²àµÄ²éѯ·µ»ØÓÒ²à²éѯûÓÐÕÒµ½µÄËùÓзÇÖظ´Öµ¡£
SELECT * from TableA EXCEPT
SELECT * from TableB;
ÏÂÃæÊǽá¹û¼¯£º
Col1
-----------
2
(1 row(s) affected)
B. ʹÓà INTERSECT
ÔÚʾÀýÖÐʹÓà TableA ºÍ TableB ÖеÄÊý¾Ý¡£
TableA (col1 int)
TableB (col1 int)
NULL
2
NULL
2
NULL
2
1
4
2
4
2
2
3
4
4
ÒÔϲéѯ·µ»Ø INTERSECT
²Ù×÷Êý×óÓÒÁ½²àµÄÁ½¸ö²éѯ¾ù·µ»ØµÄËùÓзÇÖظ´Öµ¡£
SELECT * from TableA INTERSECT
SELECT * from TableB;
ÏÂÃæÊǽá¹û¼¯£º
Col1
-----------
2
4
(2 row(s) affected)
Ïà¹ØÎĵµ£º
delete from
uservalid
where(lastlogin<'2007-1-1
0:0:00')
Çå³ýÔÚuservalid±íÖÐ×îºóµÇ½ʱ¼äÔÚ2007Äê1ÔÂ1ÈÕÁãʱÁã·Ö֮ǰµÄÊý¾Ý
²éѯijһ·¶Î§ÄÚµÄÊý¾ÝÔòselete * from uservalid
where(wealth in(500,1000))
ÔÚ²éѯ·ÖÎöÆ÷ÀïÃæselete from where
inÕâЩ¹Ø¼ü×Ö¶¼×Ô¶¯´óдÁË
......
ÔÚÕâÀï,ÎÒËùÖ¸µÄ°æ±¾ÊÇÖ¸µÄ2000,2005,2008ÕâÑùµÄ°æ±¾Çø±ð,¶ø²»ÊÇÆóÒµ°æ,¿ª·¢°æÕâÑùµÄ°æ±¾Çø±ð.´Ó¹Ù·½µÄ˵·¨À´¿´,SQL ServerÊÇÔÊÐíÊý¾Ý¿â´ÓµÍ°æ±¾Ïò¸ß°æ±¾»Ö¸´,µ«²»Ö§³Ö¸ß°æ±¾ÏòµÍ°æ±¾µÄ»Ö¸´,Èç¹ûÎÒÃÇÓø߰汾µÄÊý¾Ý¿âÔڵͰ汾µÄÊý¾Ý¿âÒýÇæÏ»ָ´,»á³öÏÖʲô´íÎóÃèÊöÄØ?
......
MS SQL Server²éѯÓÅ»¯·½·¨
×÷Õߣºxmllover 2007-11-29
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
4¡¢ÄÚ´æ ......
Ò»¡¢SQL´æ´¢¹ý³ÌµÄ¸ÅÄÓŵ㼰Óï·¨
¡¡¡¡ÕûÀíÔÚѧϰ³ÌÐò¹ý³Ì֮ǰ£¬ÏÈÁ˽âÏÂʲôÊÇ´æ´¢¹ý³Ì?ΪʲôҪÓô洢¹ý³Ì£¬ËûÓÐÄÇЩÓŵã
¡¡¡¡¶¨Ò壺½«³£ÓõĻòºÜ¸´ÔӵŤ×÷£¬Ô¤ÏÈÓÃSQLÓï¾äдºÃ²¢ÓÃÒ»¸öÖ¸¶¨µÄÃû³Æ´æ´¢ÆðÀ´, ÄÇôÒÔºóÒª½ÐÊý¾Ý¿âÌṩÓëÒѶ¨ÒåºÃµÄ´æ´¢¹ý³ÌµÄ¹¦ÄÜÏàͬµÄ·þÎñʱ,Ö»Ðèµ÷ÓÃexecute,¼´¿É×Ô¶¯Íê³ ......
ÔÚSQL Server 2005 Express Éϸ½¼Ó´ÓÁíÍâһ̨µçÄÔCopy¹ýÀ´µÄÊý¾Ý¿âºó£¬Êý¾Ý¿âΪ“Ö»¶Á”¡£
½â¾ö°ì·¨£º
´ò¿ª SQL Server Configuration Manager, ´ò¿ªSQL Server SQLEXPRESS µÄÊôÐÔ
ÔÚÄÚÖÃÕʺŴ¦£¬°Ñ“ÍøÂç·þÎñ”¸Ä³É“±¾µØϵͳ”£¬ÖØÐÂÆô¶¯SQL Server 2005 Express ºó£¬ÔÙ¸½¼ÓÊý¾Ý ......