{"id":690,"date":"2018-05-30T12:50:53","date_gmt":"2018-05-30T04:50:53","guid":{"rendered":"https:\/\/blog.freesilo.com\/?p=690"},"modified":"2018-05-30T12:50:53","modified_gmt":"2018-05-30T04:50:53","slug":"alter-table-%e6%9b%b4%e6%94%b9%e7%ba%a6%e6%9d%9f%e5%92%8c%e5%a4%96%e9%94%ae%ef%bc%8ccase-when-then-%ef%bc%8c%e8%81%94%e5%90%88%e5%8f%98%e6%8d%a2%e7%9a%84sql%e8%af%ad%e5%8f%a5","status":"publish","type":"post","link":"https:\/\/freesilo.com\/?p=690","title":{"rendered":"Alter Table \u66f4\u6539\u7ea6\u675f\u548c\u5916\u952e\uff0cCase When Then \uff0c\u8054\u5408\u53d8\u6362\u7684SQL\u8bed\u53e5"},"content":{"rendered":"<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nselect *,(case when type=1 then \r\n    (select typename from TableaType where type=1)\r\n    when type=2 then\r\n    (select typename from TableaType where type=2)\r\n    when type=3 then\r\n    (select typename from TableaType where type=3)\r\n    else \r\n    (select typename from TableaType where type=4)\r\n    end\r\n) as TypeTmp from Tablea\r\n\r\n=================================\r\n\r\nselect *,(select typename from tableType where type=t.type) as type1 \r\nfrom tablea t\r\n\r\n==================================\r\n--\u4fee\u6539\u8868\u6dfb\u52a0\u4e3b\u952e\r\nALTER TABLE &#x5B;tablea] WITH NOCHECK ADD \r\n     PRIMARY KEY  CLUSTERED \r\n    (\r\n        &#x5B;ID]\r\n    )  ON &#x5B;PRIMARY] \r\nGO\r\n--\u4fee\u6539\u8868\u6dfb\u52a0\u9ed8\u8ba4\u503c\u548c\u552f\u4e00\u7ea6\u675f\r\nALTER TABLE &#x5B;tablea] WITH NOCHECK ADD \r\n  CONSTRAINT &#x5B;DF__tableb__TypeN__6346339D] DEFAULT ('') FOR &#x5B;TypeName],\r\n  CONSTRAINT &#x5B;DF__tableb__TypeR__643A57D6] DEFAULT ('') FOR &#x5B;TypeRemark],\r\n  CONSTRAINT &#x5B;UQ__tablea__Type] UNIQUE  NONCLUSTERED \r\n    (\r\n        &#x5B;Type]\r\n    )  ON &#x5B;PRIMARY] \r\nGO\r\n\r\n--\u4fee\u6539\u7cfb\u8868\u7684Type\u5b57\u6bb5\u6dfb\u52a0\u5916\u952e\u7ea6\u675f\u5230\u53e6\u4e00\u4e2a\u8868\r\nALTER TABLE &#x5B;tablec] ADD \r\n    CONSTRAINT &#x5B;FK_tablec_tablea] FOREIGN KEY \r\n    (\r\n        &#x5B;Type]\r\n    ) REFERENCES &#x5B;tablea] (\r\n        &#x5B;Type]\r\n    )\r\nGO\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>select *,(case when type=1 then (select &#8230;<br \/><a class=\"read-more-button\" href=\"https:\/\/freesilo.com\/?p=690\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-690","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/690","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=690"}],"version-history":[{"count":1,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/690\/revisions"}],"predecessor-version":[{"id":694,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/690\/revisions\/694"}],"wp:attachment":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=690"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=690"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=690"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}