{"id":196,"date":"2010-01-29T11:15:00","date_gmt":"2010-01-29T03:15:00","guid":{"rendered":"http:\/\/blog.infinity.idv.tw\/index.php\/2010\/01\/29\/sql%e8%aa%9e%e6%b3%95-left-join\/"},"modified":"2010-01-29T11:15:00","modified_gmt":"2010-01-29T03:15:00","slug":"sql%e8%aa%9e%e6%b3%95-left-join","status":"publish","type":"post","link":"http:\/\/blog.infinity.idv.tw\/?p=196","title":{"rendered":"SQL\u8a9e\u6cd5 Left Join"},"content":{"rendered":"

\u770b\u79d1\u5b78\u5bb6\u7814\u7a76\uff0c\u4eba\u6703\u89ba\u5f97\u8d70\u5728\u5de6\u908a\u7684\u4eba\u6bd4\u8f03\u6709\u7537(\u5973)\u4eba\u5473\uff0c\u56e0\u6b64\u60f3\u6ce1\u599e\u7684\u8acb\u8d70\u5728\u5973\u4f34\u7684\u5de6\u908a\u2026\u2026\u2026\u2026\u2026\u4e0d\u662f\u6c92\u6709\u9053\u7406\u7684\uff0c\u53f0\u7063\u5c6c\u65bc\u9760\u53f3\u908a\u8d70\u7684\u7fd2\u6163\uff0c\u5982\u679c\u7537\u58eb\u5728\u5973\u751f\u5de6\u908a\uff0c\u6703\u6709\u4fdd\u8b77\u5973\u751f\u8b93\u5973\u751f\u8d70\u6bd4\u8f03\u8def\u908a\u6bd4\u8f03\u5b89\u5168\u7684\u611f\u89ba……………………….\u518d\u8aaa\u9023\u96fb\u8166SQL\u8a9e\u6cd5\u4e5f\u662f\u8981\u4f60\u5728\u5de6\u908a\u5462!!!!<\/p>\n

 <\/p>\n

\u6628\u5929\u53bb\u66f8\u5e97\u525b\u597d\u770b\u5230\u6709\u4ecb\u7d39Join\u7684\u66f8\u7ffb\u4e86\u4e00\u4e0b\uff0c\u767c\u73fe left join \u6b63\u7b26\u5408\u6211\u7684\u9700\u6c42\uff0c\u8a66\u4e86\u4e09\u500bTable\u7684 left Join\u4e00\u6b21\u628a\u6211\u8981\u7684\u90fd\u5217\u8868\u51fa\u4f86\uff0c\u7701\u4e86\u53e6\u5916\u5169\u500b\u984d\u5916\u67e5\u8a62\uff0c\u770b\u6587\u737b\u4e5f\u8aaa\u4f7f\u7528join\u6bd4\u8f03\u6709\u6548\u7387\u3002<\/p>\n

select g.*, cbas.custid, cbas.cmpabv, cntc.cntcid, cntc.phone from dbo.gdsinfomt g
left join dbo.custbas cbas on (g.Agent = cbas.custid)
left join dbo.custcntc cntc on ((g.Agent = cntc.custid) and (cntc.cntcid = substring(g.declno,1,2)))
where g.senddate >= ‘20100128’
and g.senddate <= ‘20100128’
and g.Gdsno like ‘D%’<\/p>\n

\u5982\u679c\u662f\u571f\u6cd5\u934a\u92fc\u7684\u505a\u6cd5\u6703\u6709\u4e09\u500b\u968e\u6bb5\u7684\u67e5\u8a62\uff0c\u6703\u5148\u67e5\u8a62<\/p>\n

select g.* from dbo.gdsinfomt g
where g.senddate >= ‘20100128’
and g.senddate <= ‘20100128’
and g.Gdsno like ‘D%’<\/p>\n

\u7136\u5f8c\u5728 select g.* from dbo.gdsinfomt g \u8cc7\u6599\u56de\u5708\u5167\u518d\u4e00\u7b46\u4e00\u7b46\u4f9d g.Agent = cbas.custid \u70ba\u689d\u4ef6\u67e5\u8a62\u8981\u7684\u8cc7\u6599<\/p>\n

select * from dbo.custbas cbas<\/p>\n

where cbas.custid = g.Agent(\u4e0a\u500b\u67e5\u8a62\u67e5\u5230\u7684\u8cc7\u6599)<\/p>\n

\u7b2c\u4e09\u500bTABLE\u5728 select g.* from dbo.gdsinfomt g \u8cc7\u6599\u56de\u5708\u5167\u518d\u4e00\u7b46\u4e00\u7b46\u4f9d g.Agent = cntc.custid and cntc.cntcid = substring(g.declno,1,2) \u9019\u5169\u500b\u689d\u4ef6\u67e5\u8a62<\/p>\n

select * from dbo.custcntc cntc<\/p>\n

where g.Agent = cntc.custid<\/p>\n

and cntc.cntcid = substring(g.declno,1,2)<\/p>\n","protected":false},"excerpt":{"rendered":"

\u770b\u79d1\u5b78\u5bb6\u7814\u7a76\uff0c\u4eba\u6703\u89ba\u5f97\u8d70\u5728\u5de6\u908a\u7684\u4eba\u6bd4\u8f03\u6709\u7537(\u5973)\u4eba\u5473\uff0c\u56e0\u6b64\u60f3\u6ce1\u599e\u7684\u8acb\u8d70\u5728\u5973\u4f34\u7684\u5de6\u908a\u2026\u2026\u2026\u2026\u2026\u4e0d\u662f\u6c92\u6709\u9053\u7406\u7684\uff0c\u53f0\u7063\u5c6c\u65bc\u9760\u53f3\u908a\u8d70\u7684\u7fd2\u6163\uff0c\u5982\u679c\u7537\u58eb\u5728\u5973\u751f\u5de6\u908a\uff0c\u6703\u6709\u4fdd\u8b77\u5973\u751f\u8b93\u5973\u751f\u8d70\u6bd4\u8f03\u8def\u908a\u6bd4\u8f03\u5b89\u5168\u7684\u611f\u89ba……………………….\u518d\u8aaa\u9023\u96fb\u8166SQL\u8a9e\u6cd5\u4e5f\u662f\u8981\u4f60\u5728\u5de6\u908a\u5462!!!!<\/p>\n

 <\/p>\n

\u6628\u5929\u53bb\u66f8\u5e97\u525b\u597d\u770b\u5230\u6709\u4ecb\u7d39Join\u7684\u66f8\u7ffb\u4e86\u4e00\u4e0b\uff0c\u767c\u73fe left join \u6b63\u7b26\u5408\u6211\u7684\u9700\u6c42\uff0c\u8a66\u4e86\u4e09\u500bTable\u7684 left Join\u4e00\u6b21\u628a\u6211\u8981\u7684\u90fd\u5217\u8868\u51fa\u4f86\uff0c\u7701\u4e86\u53e6\u5916\u5169\u500b\u984d\u5916\u67e5\u8a62\uff0c\u770b\u6587\u737b\u4e5f\u8aaa\u4f7f\u7528join\u6bd4\u8f03\u6709\u6548\u7387\u3002<\/p>\n

select g.*, cbas.custid, cbas.cmpabv, cntc.cntcid, cntc.phone from dbo.gdsinfomt g left join dbo.custbas cbas on (g.Agent = cbas.custid) left join dbo.custcntc cntc on ((g.Agent = cntc.custid) and (cntc.cntcid = substring(g.declno,1,2))) where g.senddate >= ‘20100128’ and g.senddate <= ‘20100128’ […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[27],"tags":[206],"_links":{"self":[{"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/196"}],"collection":[{"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=196"}],"version-history":[{"count":0,"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/196\/revisions"}],"wp:attachment":[{"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=196"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=196"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.infinity.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=196"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}