{"id":2639,"date":"2017-12-19T10:22:02","date_gmt":"2017-12-19T02:22:02","guid":{"rendered":"http:\/\/jason695.why3s.tw\/wordpress\/?p=2639"},"modified":"2017-12-21T11:37:33","modified_gmt":"2017-12-21T03:37:33","slug":"%e6%9c%80%e5%a4%a7%e7%be%a4%e7%b5%84","status":"publish","type":"post","link":"https:\/\/jason695.why3s.tw\/wordpress\/2017\/12\/19\/%e6%9c%80%e5%a4%a7%e7%be%a4%e7%b5%84\/","title":{"rendered":"\u6700\u5927\u7fa4\u7d44\u95dc\u806f"},"content":{"rendered":"<p>\u8fd1\u671f\u9047\u5230\u6700\u96e3\u7684\u908f\u8f2f<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-931.jpg\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2642\" src=\"https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-931.jpg?resize=712%2C472\" alt=\"\" width=\"712\" height=\"472\" srcset=\"https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-931.jpg?w=976&amp;ssl=1 976w, https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-931.jpg?resize=300%2C199&amp;ssl=1 300w, https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-931.jpg?resize=768%2C509&amp;ssl=1 768w\" sizes=\"auto, (max-width: 712px) 100vw, 712px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-921-1.jpg\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2641 size-large\" src=\"https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-921-1-1024x533.jpg?resize=712%2C371\" alt=\"\" width=\"712\" height=\"371\" srcset=\"https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-921-1.jpg?resize=1024%2C533&amp;ssl=1 1024w, https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-921-1.jpg?resize=300%2C156&amp;ssl=1 300w, https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-921-1.jpg?resize=768%2C399&amp;ssl=1 768w, https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-921-1.jpg?w=1623&amp;ssl=1 1623w, https:\/\/i0.wp.com\/jason695.why3s.tw\/wordpress\/wp-content\/uploads\/Image-921-1.jpg?w=1424&amp;ssl=1 1424w\" sizes=\"auto, (max-width: 712px) 100vw, 712px\" \/><\/a><\/p>\n<pre class=\"lang:tsql decode:true \">if object_id('tempdb..#IL_Benson') is not null\r\n\tdrop table #IL_Benson ;\r\n\r\ncreate table #IL_Benson (\r\n\tActSeq varchar(19) --18\r\n\t, GCNo varchar(21) --21\r\n\t, ActSeq_T varchar(19)\r\n\t, GCNo_T varchar(21)\r\n\t, RUN int default 0) ;\r\n\r\ninsert into #IL_Benson (ActSeq, GCNo)\r\nvalues ('ACT1', 'A')\r\n, ('ACT2', 'B'), ('ACT2', 'C'), ('ACT2', 'D')\r\n, ('ACT3', 'A'), ('ACT3', 'C')\r\n, ('ACT4', 'D')\r\n, ('ACT5', 'D'), ('ACT5', 'E')\r\n, ('ACT6', 'F'), ('ACT6', 'G')\r\n, ('ACT7', 'F')\r\n, ('ACT8', 'G'), ('ACT8', 'H')\r\n, ('ACT9', 'Z')\r\n\r\n\/*\r\nupdate a\r\nset a.RUN = 0\r\n\t, a.ActSeq_T = null\r\n\t, a.GCNo_T = null\r\nfrom #IL_Benson a\r\n\r\nselect a.ActSeq, a.GCNo, a.ActSeq_T, a.GCNo_T, a.RUN\r\nfrom #IL_Benson a\r\norder by 1, 2\r\n*\/\r\n--\u8cc7\u6599\u6aa2\u6e2c,RUN&lt;0\r\n--(RUN=-1) 1 ACT to 1 GC\r\nupdate a\r\nset a.ActSeq_T = a.ActSeq\r\n\t, a.GCNo_T = a.GCNo\r\n\t, a.RUN = -1\r\nfrom #IL_Benson a\r\ninner join (\r\n\tselect a.ActSeq, count(distinct  a.GCNo) CNT\r\n\tfrom #IL_Benson a\r\n\tgroup by a.ActSeq\r\n\thaving count(distinct  a.GCNo) = 1 ) b\r\n\ton a.ActSeq = b.ActSeq\r\ninner join (\r\n\tselect a.GCNo, count(distinct a.ActSeq) CNT\r\n\tfrom #IL_Benson a\r\n\tgroup by a.GCNo\r\n\thaving count(distinct  a.ActSeq) = 1 ) c\r\n\ton a.GCNo = c.GCNo\r\n;\r\n\r\n--(RUN=-2) 1 ACT to Multi GC and 1 GC to 1 ACT\r\nupdate a\r\nset a.ActSeq_T = a.ActSeq\r\n\t, a.GCNo_T = b.GCNo_T\r\n\t, a.RUN = -2\r\n--select count(*)\r\nfrom #IL_Benson a\r\ninner join (\r\n\tselect a.ActSeq, count(distinct  a.GCNo) CNT, min(a.GCNo) GCNo_T\r\n\tfrom #IL_Benson a\r\n\tgroup by a.ActSeq\r\n\thaving count(distinct  a.GCNo) &gt; 1 ) b\r\n\ton a.ActSeq = b.ActSeq\r\ninner join (\r\n\tselect b.ActSeq, count(distinct a.ActSeq) cnt\r\n\tfrom #IL_Benson a\r\n\tinner join (\r\n\t\tselect a.ActSeq, a.GCNo\r\n\t\tfrom #IL_Benson a\r\n\t\tinner join (\r\n\t\t\tselect a.ActSeq, count(distinct  a.GCNo) CNT\r\n\t\t\tfrom #IL_Benson a\r\n\t\t\tgroup by a.ActSeq\r\n\t\t\thaving count(distinct  a.GCNo) &gt; 1 ) b\r\n\t\t\ton a.ActSeq = b.ActSeq ) b\r\n\t\ton a.GCNo = b.GCNo\r\n\tgroup by b.ActSeq\r\n\thaving count(distinct a.ActSeq) = 1 ) c\r\n\ton a.ActSeq = c.ActSeq\r\nwhere a.ActSeq_T is null\r\nand a.GCNo_T is null\r\n;\r\n\r\n--(RUN=-3) 1 ACT to 1 GC and 1 GC to Multi ACT\r\nupdate a\r\nset a.ActSeq_T = b.ActSeq_T\r\n\t, a.GCNo_T = a.GCNo\r\n\t, a.RUN = -3\r\n--select count(*)\r\nfrom #IL_Benson a\r\ninner join (\r\n\tselect a.GCNo, count(distinct a.ActSeq) CNT, min(a.ActSeq) ActSeq_T\r\n\tfrom #IL_Benson a\r\n\tgroup by a.GCNo\r\n\thaving count(distinct  a.ActSeq) &gt; 1 ) b\r\n\ton a.GCNo = b.GCNo\r\ninner join (\r\n\tselect b.GCNo, count(distinct a.GCNo) cnt\r\n\tfrom #IL_Benson a\r\n\tinner join (\r\n\t\tselect a.ActSeq, a.GCNo\r\n\t\tfrom #IL_Benson a\r\n\t\tinner join (\r\n\t\t\tselect a.GCNo, count(distinct a.ActSeq) CNT\r\n\t\t\tfrom #IL_Benson a\r\n\t\t\tgroup by a.GCNo\r\n\t\t\thaving count(distinct a.ActSeq) &gt; 1 ) b\r\n\t\t\ton a.GCNo = b.GCNo ) b\r\n\t\ton a.ActSeq = b.ActSeq\r\n\tgroup by b.GCNo\r\n\thaving count(distinct a.GCNo) = 1\r\n\t) c\r\n\ton a.GCNo = c.GCNo\r\nwhere a.ActSeq_T is null\r\nand a.GCNo_T is null\r\n;\r\n\r\n--select * from #IL_Benson\r\n--------------------------------------\r\n--\u88dc\u884d\u4f38\u95dc\u806f\u8cc7\u6599,RUN=1\r\nDECLARE @RC INT\r\nDECLARE @RUN INT\r\n\r\nselect @RC = count(*)\r\n--select count(*)\r\nfrom #IL_Benson a\r\nwhere a.ActSeq_T is null\r\n\r\nWHILE @RC &gt; 0\r\nBEGIN\r\n\r\n\tSET @RC = 0\r\n\r\n\tinsert into #IL_Benson (ActSeq, GCNo, RUN)\r\n\tselect distinct b.ActSeq_T ActSeq, a.GCNo, 1\r\n\tfrom #IL_Benson a\r\n\tinner join (\r\n\t\tselect distinct b.ActSeq ActSeq_T, a.ActSeq, b.GCNo\r\n\t\tfrom #IL_Benson a\r\n\t\tinner join (\r\n\t\t\tselect distinct a.ActSeq, a.GCNo\r\n\t\t\tfrom #IL_Benson a\r\n\t\t\twhere a.ActSeq_T is null\r\n\t\t\t--and a.ActSeq = '3'\r\n\t\t\t) b\r\n\t\t\ton a.GCNo = b.GCNo ) b\r\n\t\ton a.ActSeq = b.ActSeq\r\n\tleft outer join #IL_Benson c\r\n\t\ton b.ActSeq_T = c.ActSeq\r\n\t\tand a.GCNo = c.GCNo\r\n\t\tand c.ActSeq_T is null\r\n\twhere a.ActSeq_T is null\r\n\tand c.ActSeq is null\r\n\r\n\tset @RC = @RC + @@ROWCOUNT\r\n\t--select * from #IL_Benson where Run=1\r\n\r\n\tinsert into #IL_Benson (ActSeq, GCNo, RUN)\r\n\tselect distinct a.ActSeq, b.GCNo_T GCNo, 1\r\n\tfrom #IL_Benson a\r\n\tinner join (\r\n\t\tselect distinct b.GCNo GCNo_T, b.ActSeq, a.GCNo\r\n\t\tfrom #IL_Benson a\r\n\t\tinner join (\r\n\t\t\tselect distinct a.ActSeq, a.GCNo\r\n\t\t\tfrom #IL_Benson a\r\n\t\t\twhere a.ActSeq_T is null\r\n\t\t\t--and a.GCNo = 'D'\r\n\t\t\t) b\r\n\t\t\ton a.ActSeq = b.ActSeq ) b\r\n\t\ton a.GCNo = b.GCNo\r\n\tleft outer join #IL_Benson c\r\n\t\ton a.ActSeq = c.ActSeq\r\n\t\tand b.GCNo_T = c.GCNo\r\n\t\tand c.ActSeq_T is null\r\n\twhere a.ActSeq_T is null\r\n\tand c.ActSeq is null\r\n\r\n\tset @RC = @RC + @@ROWCOUNT\r\n\t--select * from #IL_Benson where Run=1\r\n\r\n\t--select a.RUN, count(*)\r\n\t--from #IL_Benson a\r\n\t--where a.ActSeq_T is null\r\n\t--group by a.RUN\r\nEND\r\n\r\nupdate a\r\nset a.ActSeq_T = a.ActSeq\r\n\t, a.GCNo_T = a.GCNo\r\nfrom #IL_Benson a\r\nwhere a.ActSeq_T is null\r\nand a.GCNo_T is null\r\n\r\n--select * from #IL_Benson order by ActSeq_T,GCNo_T\r\n--------------------------------------\r\ndeclare @RC INT\r\nselect @RC = count(*)\r\nfrom #IL_Benson a\r\nwhere a.RUN &gt;= 0\r\n--select @RC\r\n\r\nWHILE @RC &gt; 0\r\nBEGIN\r\n\r\n\tSET @RC = 0\r\n\r\n\tupdate a\r\n\tset a.ActSeq_T = b.ActSeq\r\n\t--select count(*)\r\n\tfrom #IL_Benson a\r\n\tinner join (\r\n\t\tselect a.GCNo GCNo, min(a.ActSeq) ActSeq\r\n\t\tfrom #IL_Benson a\r\n\t\twhere a.RUN &gt;= 0\r\n\t\tgroup by a.GCNo ) b\r\n\t\ton a.GCNo_T = b.GCNo\r\n\t\tand a.ActSeq_T &lt;&gt; b.ActSeq\r\n\twhere a.RUN &gt;= 0\r\n\r\n\tSET @RC = @RC +  @@ROWCOUNT\r\n\r\n\tupdate a\r\n\tset a.GCNo_T = b.GCNo\r\n\t--select count(*)\r\n\tfrom #IL_Benson a\r\n\tinner join (\r\n\t\tselect a.ActSeq ActSeq, min(a.GCNo) GCNo\r\n\t\tfrom #IL_Benson a\r\n\t\twhere a.RUN &gt;= 0\r\n\t\tgroup by a.ActSeq ) b\r\n\t\ton a.ActSeq_T = b.ActSeq\r\n\t\tand a.GCNo_T &lt;&gt; b.GCNo\r\n\twhere a.RUN &gt;= 0\r\n\r\n\tSET @RC = @RC +  @@ROWCOUNT\r\n\r\nEND\r\n\r\n--select * from #IL_Benson order by ActSeq,GCNo<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u8fd1\u671f\u9047\u5230\u6700\u96e3\u7684\u908f\u8f2f if object_id(&#8216;tempdb..#IL_Benson&#8217;) is not nul &hellip; <a href=\"https:\/\/jason695.why3s.tw\/wordpress\/2017\/12\/19\/%e6%9c%80%e5%a4%a7%e7%be%a4%e7%b5%84\/\" class=\"more-link\">\u95b1\u8b80\u5168\u6587 <span class=\"screen-reader-text\">\u6700\u5927\u7fa4\u7d44\u95dc\u806f<\/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,5],"tags":[],"class_list":["post-2639","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-5","no-featured-image"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/2639","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=2639"}],"version-history":[{"count":0,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/2639\/revisions"}],"wp:attachment":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/media?parent=2639"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/categories?post=2639"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/tags?post=2639"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}