{"id":2493,"date":"2016-01-13T17:23:41","date_gmt":"2016-01-13T09:23:41","guid":{"rendered":"http:\/\/jason695.why3s.tw\/wordpress\/?p=2493"},"modified":"2016-01-13T17:24:46","modified_gmt":"2016-01-13T09:24:46","slug":"sql%e5%b0%87%e5%a4%9a%e7%ad%86%e8%b3%87%e6%96%99%e5%90%88%e4%bd%b5%e7%82%ba%e4%b8%80%e7%ad%86%e9%a1%af%e7%a4%bafor-xml-path","status":"publish","type":"post","link":"https:\/\/jason695.why3s.tw\/wordpress\/2016\/01\/13\/sql%e5%b0%87%e5%a4%9a%e7%ad%86%e8%b3%87%e6%96%99%e5%90%88%e4%bd%b5%e7%82%ba%e4%b8%80%e7%ad%86%e9%a1%af%e7%a4%bafor-xml-path\/","title":{"rendered":"[SQL]\u5c07\u591a\u7b46\u8cc7\u6599\u5408\u4f75\u70ba\u4e00\u7b46\u986f\u793a(FOR XML PATH)"},"content":{"rendered":"
\n
\n

\u539f\u6587:[SQL]\u5c07\u591a\u7b46\u8cc7\u6599\u5408\u4f75\u70ba\u4e00\u7b46\u986f\u793a(FOR XML PATH)<\/a><\/p>\n<\/div>\n

\u7528\u5317\u98a8\u8cc7\u6599\u5eab\u8209\u4f8b<\/p>\n

step1:\u5148\u8b93\u6211\u5011\u770b\u770borderID = ‘10248’\u7684\u7522\u54c1\u6709\u54ea\u4e9b<\/p>\n

\r\nSELECT<\/span> * FROM<\/span> [Order<\/span> Details] \r\nwhere<\/span> OrderID = '10248'<\/span><\/span><\/code><\/pre>\n
\u6709\u9019\u4e9b<\/p>\n
step2:\u7136\u5f8c\u6211\u5011\u5229\u7528for xml path\u628a\u4ed6\u5408\u4f75\u986f\u793a<\/p>\n
\r\nSELECT<\/span> cast<\/span>(ProductID AS<\/span> NVARCHAR<\/span> ) + ','<\/span> from<\/span> [Order<\/span> Details] \r\nwhere<\/span> OrderID = '10248'<\/span>\r\nFOR<\/span> XML<\/span> PATH<\/span>(''<\/span>)<\/span><\/code><\/pre>\n
\u8f38\u51fa\u662f\u9019\u6a23\uff0c\u53ef\u4ee5\u767c\u73fe\u679c\u7136productID\u90fd\u5408\u4f75\u4e86<\/p>\n
step3:\u7136\u5f8c\u6539\u6210\u986f\u793a\u6240\u6709\u7684order\u7684productID<\/p>\n
\r\nSELECT<\/span> OrderID,(SELECT<\/span> cast<\/span>(ProductID AS<\/span> NVARCHAR<\/span> ) + ','<\/span> from<\/span> [Order<\/span> Details] \r\nwhere<\/span> OrderID = ord<\/span>.OrderID\r\nFOR<\/span> XML<\/span> PATH<\/span>(''<\/span>)) as<\/span> productIDs\r\nfrom<\/span> orders ord<\/span>\r\nGROUP<\/span> BY<\/span> orderid<\/span><\/code><\/pre>\n
\u8f38\u51fa\u50cf\u662f\u9019\u6a23\uff0c\u6240\u6709\u7684order\u7684productID\uff0c\u4f46\u662fproductID\u9084\u591a\u4e00\u500b\u9017\u865f<\/p>\n
step4:\u6700\u5f8c\u52d5\u4e9b\u624b\u8173\uff0c\u8f38\u51fa\u5c31\u5b8c\u7f8e\u4e86\uff0c\u6210\u529f\u7684\u5c07\u591a\u7b46\u8cc7\u6599\u5408\u4f75\u70ba\u4e00\u7b46<\/p>\n
\r\nSELECT<\/span> m<\/span>.OrderID ,left<\/span>(m<\/span>.productIDs,len<\/span>(m<\/span>.productIDs)-1<\/span>) as<\/span> productIDsFinal from<\/span> \r\n(SELECT<\/span> OrderID,(SELECT<\/span> cast<\/span>(ProductID AS<\/span> NVARCHAR<\/span> ) + ','<\/span> from<\/span> [Order<\/span> Details] \r\nwhere<\/span> OrderID = ord<\/span>.OrderID\r\nFOR<\/span> XML<\/span> PATH<\/span>(''<\/span>)) as<\/span> productIDs\r\nfrom<\/span> orders ord<\/span>\r\nGROUP<\/span> BY<\/span> orderid) M<\/span> --\u9019\u500bM\u4e00\u5b9a\u8981\u52a0\uff0c\u4e0d\u77e5\u9053\u70ba\u5565<\/span>\r\nORDER<\/span> by<\/span> M<\/span>.OrderID <\/span><\/code><\/pre>\n
\u6700\u5f8c\u8f38\u51fa:<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
\u539f\u6587:[SQL]\u5c07\u591a\u7b46\u8cc7\u6599\u5408\u4f75\u70ba\u4e00\u7b46\u986f\u793a(FOR XML PATH) \u7528\u5317\u98a8\u8cc7\u6599\u5eab\u8209\u4f8b step1:\u5148\u8b93\u6211\u5011\u770b … \u95b1\u8b80\u5168\u6587 [SQL]\u5c07\u591a\u7b46\u8cc7\u6599\u5408\u4f75\u70ba\u4e00\u7b46\u986f\u793a(FOR XML PATH)<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[47],"tags":[],"class_list":["post-2493","post","type-post","status-publish","format-standard","hentry","category-sql-server","no-featured-image"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/2493","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/comments?post=2493"}],"version-history":[{"count":0,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/2493\/revisions"}],"wp:attachment":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/media?parent=2493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/categories?post=2493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/tags?post=2493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\"\"<\/a><\/p>\n<\/div>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n