sqlÖ®left join¡¢right join¡¢inner joinµÄÇø±ð
left join(×óÁª½Ó) ·µ»Ø°üÀ¨×ó±íÖеÄËùÓмǼºÍÓÒ±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
right join(ÓÒÁª½Ó) ·µ»Ø°üÀ¨ÓÒ±íÖеÄËùÓмǼºÍ×ó±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
inner join(µÈÖµÁ¬½Ó) Ö»·µ»ØÁ½¸ö±íÖÐÁª½á×Ö¶ÎÏàµÈµÄÐÐ
¾ÙÀýÈçÏ£º
--------------------------------------------
±íA¼Ç¼ÈçÏ£º
aID¡¡¡¡¡¡¡¡¡¡aNum
1¡¡¡¡¡¡¡¡¡¡a20050111
2¡¡¡¡¡¡¡¡¡¡a20050112
3¡¡¡¡¡¡¡¡¡¡a20050113
4¡¡¡¡¡¡¡¡¡¡a20050114
5¡¡¡¡¡¡¡¡¡¡a20050115
±íB¼Ç¼ÈçÏÂ:
bID¡¡¡¡¡¡¡¡¡¡bName
1¡¡¡¡¡¡¡¡¡¡2006032401
2¡¡¡¡¡¡¡¡¡¡2006032402
3¡¡¡¡¡¡¡¡¡¡2006032403
4¡¡¡¡¡¡¡¡¡¡2006032404
8¡¡¡¡¡¡¡¡¡¡2006032408
--------------------------------------------
1.left join
sqlÓï¾äÈçÏÂ:
select * from A
left join B
on A.aID = B.bID
½á¹ûÈçÏÂ:
aID¡¡¡¡¡¡¡¡¡¡aNum¡¡¡¡¡¡¡¡¡¡bID¡¡¡¡¡¡¡¡¡¡bName
1¡¡¡¡¡¡¡¡¡¡a20050111¡¡¡¡¡¡¡¡1¡¡¡¡¡¡¡¡¡¡2006032401
2¡¡¡¡¡¡¡¡¡¡a20050112¡¡¡¡¡¡¡¡2¡¡¡¡¡¡¡¡¡¡2006032402
3¡¡¡¡¡¡¡¡¡¡a20050113¡¡¡¡¡¡¡¡3¡¡¡¡¡¡¡¡¡¡2006032403
4¡¡¡¡¡¡¡¡¡¡a20050114¡¡¡¡¡¡¡¡4¡¡¡¡¡¡¡¡¡¡2006032404
5¡¡¡¡¡¡¡¡¡¡a20050115¡¡¡¡¡¡¡¡NULL¡¡¡¡¡¡¡¡¡¡NULL
£¨ËùÓ°ÏìµÄÐÐÊýΪ 5 ÐУ©
½á¹û˵Ã÷:
left joinÊÇÒÔA±íµÄ¼Ç¼Ϊ»ù´¡µÄ,A¿ÉÒÔ¿´³É×ó±í,B¿ÉÒÔ¿´³ÉÓÒ±í,left joinÊÇÒÔ×ó±íΪ׼µÄ.
»»¾ä»°Ëµ,×ó±í(A)µÄ¼Ç¼½«»áÈ«²¿±íʾ³öÀ´,¶øÓÒ±í(B)Ö»»áÏÔʾ·ûºÏËÑË÷Ìõ¼þµÄ¼Ç¼(Àý×ÓÖÐΪ: A.aID = B.bID).
B±í¼Ç¼²»×ãµÄµØ·½¾ùΪNULL.
--------------------------------------------
2.right join
sqlÓï¾äÈçÏÂ:
select * from A
right join B
on A.aID = B.bID
½á¹ûÈçÏÂ:
aID¡¡¡¡¡¡¡¡¡¡aNum¡¡¡¡¡¡¡¡¡¡bID¡¡¡¡¡¡¡¡¡¡bName
1¡¡¡¡¡¡¡¡¡¡a20050111¡¡¡¡¡¡¡¡1¡¡¡¡¡¡¡¡¡¡2006032401
2¡¡¡¡¡¡¡¡¡¡a20050112¡¡¡¡¡¡¡¡2¡¡¡¡¡¡¡¡¡¡2006032402
3¡¡¡¡¡¡¡¡¡¡a20050113¡¡¡¡¡¡¡¡3¡¡¡¡¡¡¡¡¡¡2006032403
4¡¡¡¡¡¡¡¡¡¡a20050114¡¡¡¡¡¡¡¡4¡¡¡¡¡¡¡¡¡¡2006032404
NULL¡¡¡¡¡¡¡¡¡¡NULL¡¡¡¡¡¡¡¡¡¡8¡¡¡¡¡¡¡¡¡¡2006032408
£¨ËùÓ°ÏìµÄÐÐÊýΪ 5 ÐУ©
½á¹û˵Ã÷:
×Ðϸ¹Û²ìÒ»ÏÂ,¾Í»á·¢ÏÖ,ºÍleft joinµÄ½á¹û¸ÕºÃÏà·´,Õâ´ÎÊÇÒÔÓÒ±í(B)Ϊ»ù´¡µÄ,A±í²»×ãµÄµØ·½ÓÃNULLÌî³ä.
--------------------------------------------
3.inner join
sqlÓï¾äÈçÏÂ:
select * from A
innerjoin B
on A.aID = B.bID
½á¹ûÈçÏÂ:
aID¡¡¡¡¡¡¡¡¡¡aNum¡¡¡¡¡¡¡¡¡¡bID¡¡¡¡¡¡¡¡¡¡bName
1¡¡¡¡¡¡¡¡¡¡a200
Ïà¹ØÎĵµ£º
SQL Server 2005 hashÁª½ÓËã·¨
Èç¹ûÁ½¸öÁª½ÓÊäÈ붼ºÜ´ó£¬¶øÇÒÕâÁ½¸öÊäÈëµÄ´óС²î²»¶à£¬ÔòÔ¤ÏÈÅÅÐòµÄºÏ²¢Áª½ÓÌṩµÄÐÔÄÜÓë¹þÏ£Áª½ÓÏà½ü¡£µ«ÊÇ£¬Èç¹ûÕâÁ½¸öÊäÈëµÄ´óСÏà²îºÜ´ó£¬Ôò¹þÏ£Áª½Ó²Ù×÷ͨ³£¿ìµÃ¶à¡£
¹þÏ£Áª½Ó¿ÉÒÔÓÐЧ´¦ÀíδÅÅÐòµÄ´óÐÍ·ÇË÷ÒýÊäÈë¡£ËüÃǶԸ´ÔÓ²éѯµÄÖмä½á¹ûºÜÓÐÓã¬ÒòΪ£º
&nbs ......
Õⲿ·Ö°üº¬ÄÇЩ PostgreSQL Ö§³ÖµÄ SQL ÃüÁîµÄÐÅÏ¢£®ÕâÀïµÄ "SQL" ¾ÍÊǸÃÓïÑÔͨ³£
µÄº¬Ò壻 ÿÌõÃüÁîµÄÓë±ê×¼ÓйصļæÈÝÐÔµÄÐÅÏ¢¿ÉÒÔÔÚÏà¹ØµÄ²Î¿¼Ò³ÖÐÕÒµ½£®
Table of Contents
ABORT -- Í˳öµ±Ç°ÊÂÎñ
ALTER GROUP -- Ïò×éÖÐÔö¼ÓÓû§»ò´Ó×éÖÐɾ³ýÓû§
ALTER USER -- ¸Ä±äÊý¾Ý¿âÓû§Õʺţ®
......
TA:
1,WANG
2,ZHANG
4,LI
TB:
1,100
2,200
3,400
1.left join ×óÁ¬½Ó--ÒÔ×ó±íΪ»ù×¼£¬ÓÒ±íÖÐûֵµÄ£¬ÔÚ½á¹û¼¯ÖÐÒÔnullÖµ´úÌæ¡£(select * from TA left join TB where TA.ID=TB.ID)
1,WANG,100
2,ZHANG,200
4,NULL
2.right join ÓÒÁ¬½Ó--ÒÔÓÒ±íΪ»ù×¼£¬×ó±íÖÐûֵµÄ£¬ÔÚ ......
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ ¡¡¡¡DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)¡¡¡¡
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º¡¡¡¡
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿âCREATE ......
CREATE proc sqlToMultiExcelFile
@sqlstr nvarchar(4000), --²éѯÓï¾ä,Èç¹û²éѯÓï¾äÖÐʹÓÃÁËorder by ,Çë¼ÓÉÏtop 100 percent
@primaryKey varchar(100),--·ÖÒ³Ö÷¼ü×Ö¶Î
@path nvarchar(1000), --Îļþ´æ·ÅĿ¼
@fname nvarchar(250), --ÎļþÃû
@sheetname varchar(250)='sheet1' --Òª´´½¨µÄ¹¤×÷±íÃû,Ä ......