{"id":2349,"date":"2012-11-15T13:29:33","date_gmt":"2012-11-15T13:29:33","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2349"},"modified":"2012-11-15T13:31:30","modified_gmt":"2012-11-15T13:31:30","slug":"%e5%a4%9a%e7%8a%b6%e6%80%81%e4%b8%80%e5%ad%97%e6%ae%b5%e5%ad%98%e5%82%a8","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/11\/15\/%e5%a4%9a%e7%8a%b6%e6%80%81%e4%b8%80%e5%ad%97%e6%ae%b5%e5%ad%98%e5%82%a8\/","title":{"rendered":"\u591a\u72b6\u6001\u4e00\u5b57\u6bb5\u5b58\u50a8"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\n--\u591a\u72b6\u6001\u4e00\u5b57\u6bb5\u5b58\u50a8\uff0c\u907f\u514d\u589e\u52a0\u72b6\u6001\u9700\u8981\u518d\u65b0\u589e\u5b57\u6bb5\u7684\u4e8b\u60c5\u3002sqlserver\u4e2dset\u503c\u5373\u662f<a href=\"http:\/\/www.mssqltips.com\/sqlservertip\/1415\/determining-set-options-for-a-current-session-in-sql-server\/\">\u7c7b\u4f3c\u5b58\u50a8<\/a>\r\n--\u521b\u5efa\u72b6\u6001\u8868,\u7528\u4e8c\u8fdb\u5236\u4e0a\u76841\u6807\u8bc6\u662f\u5426\uff0c\u751f\u6210\u8eab\u4efd\u503c\u5728\u5efa\u7acb\u8868\u662f\u6307\u5b9a\uff0c\u636e\u8eab\u4efd\u503c\u53d6\u72b6\u6001\uff0c\u53ea\u9700\u8fdb\u884c\u4e0e\u8fd0\u7b97\u5373\u53ef\r\n--\u53ef\u6269\u5927\uff0c\u6bd4\u59828\u8fdb\u5236\uff0c\u6bcf\u4f4d\u4e0a\u53ef\u67098\u4e2a\u9009\u62e9,--\u5047\u5982\u67097\u79cd\u72b6\u6001\uff0c\u5219\u53ef\u7528\u4e00\u4e2a\u5b57\u6bb5\u6807\u8bc6\u51fa56\u79cd\u9009\u62e9\r\nDROP TABLE #MyStatus\r\nGO\r\n\r\nCREATE TABLE #MyStatus( StatusName NVARCHAR(100),StatusValue INT )\r\nINSERT INTO #MyStatus\r\nVALUES(&#39;\u662f\u5426\u9ad8&#39;,1) --1,001 \u4e8c\u8fdb\u5236\u6570\u4f4d\r\nINSERT INTO #MyStatus \r\nVALUES(&#39;\u662f\u5426\u5bcc&#39;,2) --2,010\r\nINSERT INTO #MyStatus\r\nVALUES(&#39;\u662f\u5426\u5e05&#39;,4) --4,100\r\n\r\nSELECT *\r\nFROM #MyStatus\r\n\r\n\r\n--\u751f\u6210\u8eab\u4efd\u503c\r\n--\u5f20\u4e09\r\nWITH zhangsan AS(\r\n\tSELECT &#39;\u662f\u5426\u9ad8&#39; StatusName,1 StatusValue\r\n\tUNION SELECT &#39;\u662f\u5426\u5bcc&#39; StatusName,0 StatusValue\r\n\tUNION SELECT &#39;\u662f\u5426\u5e05&#39; StatusName,1 StatusValue\r\n)\r\nSELECT SUM(m.StatusValue) \u5f20\u4e09\u8eab\u4efd\u503c\r\nFROM zhangsan z\r\nJOIN #MyStatus m ON z.StatusName = m.StatusName \r\nWHERE Z.StatusValue = 1\r\n\r\n--\u636e\u8eab\u4efd\u503c\u53d6\u51fa\u72b6\u6001\uff0c\u674e\u56db\u8eab\u4efd\u503c\u4e3a3\uff0c\u67e5\u8be2\u5404\u72b6\u6001\r\nSELECT m.StatusName,\r\nCASE WHEN m.StatusValue &amp; 3 = m.StatusValue THEN &#39;\u662f&#39;\r\nELSE &#39;\u5426&#39; END \r\nFROM #MyStatus m\r\n\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>&#8211;\u591a\u72b6\u6001\u4e00\u5b57\u6bb5\u5b58\u50a8\uff0c\u907f\u514d\u589e\u52a0\u72b6\u6001\u9700\u8981\u518d\u65b0\u589e\u5b57\u6bb5\u7684\u4e8b\u60c5\u3002sqlserver\u4e2dset\u503c\u5373\u662f\u7c7b\u4f3c\u5b58\u50a8 &#8211;\u521b\u5efa\u72b6\u6001 [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15],"tags":[],"class_list":["post-2349","post","type-post","status-publish","format-standard","hentry","category-15"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2349","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2349"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2349\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2349"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2349"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2349"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}