{"id":545,"date":"2010-10-01T03:43:14","date_gmt":"2010-10-01T03:43:14","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=545"},"modified":"2010-10-11T08:58:07","modified_gmt":"2010-10-11T08:58:07","slug":"%e5%a5%bd%e7%9a%84sql%e8%af%ad%e5%8f%a5","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2010\/10\/01\/%e5%a5%bd%e7%9a%84sql%e8%af%ad%e5%8f%a5\/","title":{"rendered":"\u597d\u7684SQL\u8bed\u53e5"},"content":{"rendered":"<pre lang=\"tsql\">1\u3001\u8bf4\u660e\uff1a\u590d\u5236\u8868(\u53ea\u590d\u5236\u7ed3\u6784,\u6e90\u8868\u540d\uff1aa \u65b0\u8868\u540d\uff1ab) (Access\u53ef\u7528)\r\n\u6cd5\u4e00\uff1aselect * into b from a where 1 &lt;&gt;1\r\n\u6cd5\u4e8c\uff1aselect top 0 * into b from a \r\n\r\n        \u540c\u7528\u6237\u4e0b\u522b\u4e00\u6570\u636e\u5e93\u8868\u7684\u5f15\u7528\u4e3a mydb..mytable   \u56e0\u8868\u4e0e\u5b57\u6bb5\u4e4b\u95f4\u7528\u4e00\u4e2a\u70b9\u8868\u793a\uff0c\u6545\u4e3a\u4e86\u533a\u522b\uff0c\u6570\u636e\u5e93\u4e0e\u8868\u4e4b\u95f4\u7684\u8054\u7cfb\u7528\u4e24\u4e2a\u70b9\r\n\r\n2\u3001\u8bf4\u660e\uff1a\u62f7\u8d1d\u8868(\u62f7\u8d1d\u6570\u636e,\u6e90\u8868\u540d\uff1aa \u76ee\u6807\u8868\u540d\uff1ab) (Access\u53ef\u7528)\r\ninsert into b(a, b, c) select d,e,f from b; \r\n\r\n3\u3001\u8bf4\u660e\uff1a\u8de8\u6570\u636e\u5e93\u4e4b\u95f4\u8868\u7684\u62f7\u8d1d(\u5177\u4f53\u6570\u636e\u4f7f\u7528\u7edd\u5bf9\u8def\u5f84) (Access\u53ef\u7528)\r\ninsert into b(a, b, c) select d,e,f from b in \u2018\u5177\u4f53\u6570\u636e\u5e93\u2019 where \u6761\u4ef6\r\n\u4f8b\u5b50\uff1a..\r\nfrom b in '\"&amp;Server.MapPath(\".\")&amp;\"\\data.mdb\" &amp;\"' where.. \r\n\r\n4,\u8bf4\u660e\uff1a\u65e5\u7a0b\u5b89\u6392\u63d0\u524d\u4e94\u5206\u949f\u63d0\u9192\r\nSQL: select * from \u65e5\u7a0b\u5b89\u6392 where datediff('minute',f\u5f00\u59cb\u65f6\u95f4,getdate())&gt;5 \r\n\r\n5,\u8bf4\u660e\uff1a\u968f\u673a\u53d6\u51fa10\u6761\u6570\u636e\r\nselect top 10 * from tablename order by newid() \r\n\r\n6\u3001\u8bf4\u660e\uff1a\u968f\u673a\u9009\u62e9\u8bb0\u5f55\r\nselect newid() \r\n\r\n7,\u8bf4\u660e\uff1a\u5220\u9664\u91cd\u590d\u8bb0\u5f55\r\nDelete from tablename where id not in (select max(id) from tablename group by col1,col2,...)\r\nid \u4e3a\u4e3b\u952e\uff0c\u4e3b\u952e\u4e00\u5b9a\u4e0d\u4f1a\u91cd\u590d\uff0c\u91cd\u590d\u7684\u53ea\u662fcol1,col2 \uff0c\u6309col1,col2\u5206\u7ec4\uff0c\u76f8\u540c\u7684\u5206\u5230\u4e00\u7ec4\uff0c\u6b64\u65f6\u53d6\u51fa\u6700\u5927\u7684id,\uff08min\u4e5f\u4e00\u6837\uff09\u5269\u4e0b\u7684\u5c31\u662f\u91cd\u590d\u7684\uff0c\u53bb\u6389\u5373\u53ef\u3002\r\n\u6ce8\uff1adinstinct\u53ea\u80fd\u533a\u522b\u4e00\u4e2a\u5217\u503c\uff0c\u82e5\u60f3\u5f97\u5230\u552f\u4e00\u7684\u4e00\u884c\u8bb0\u5f55\uff0c\u53ef\u7528\uff1a\r\nselect from tablename where id in( select max(id) from tablename group by col1,col2,... )\r\nselect max(id) from tablename group by col1,col2,...  \u53d6\u51fa\u8868\u4e2d\u552f\u4e00\u7684\u884c\uff0c\u90a3\u4e48\u5254\u9664\u91cd\u590d\uff0c\u552f\u4e00\u884c\u7684\u4e2a\u6570\u4e3a\uff1a\r\nselect count(1) from (select max(id) as id from tablename group by col1,col2,...) m\r\n\u4e5f\u53ef\u7528\uff1aselect count(1) from (select distinct col1,col2,... from tablename ) m\r\n\r\n8\u3001\u8bf4\u660e\uff1a\u5217\u51fa\u6570\u636e\u5e93\u91cc\u6240\u6709\u7684\u8868\u540d\r\nselect name from sysobjects where type='U' \r\n\r\n 9,\u8bf4\u660e\uff1a\u9009\u62e9\u4ece10\u523015\u7684\u8bb0\u5f55\r\nselect top 5 * from (select top 15 * from table order by id asc) table_\u522b\u540d order by id desc\r\nN\u5230M\u6761\u8bb0\u5f55(\u8981\u6709\u4e3b\u7d22\u5f15ID)\r\nSelect Top M-N * From \u8868 Where ID in (Select Top M ID From \u8868) Order by ID  Desc  \r\n\r\n10,\u6309\u59d3\u6c0f\u7b14\u753b\u6392\u5e8f:\r\nSelect * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as \r\n\r\n11,\u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u6240\u6709\u5b58\u50a8\u8fc7\u7a0b\r\nselect name as \u5b58\u50a8\u8fc7\u7a0b\u540d\u79f0 from sysobjects where xtype='P' \r\n\r\n 12,\u4e00\u6761\u8bed\u53e5\u6267\u884c\u8de8\u8d8a\u82e5\u5e72\u4e2a\u6570\u636e\u5e93\r\nselect * from OPENDATASOURCE('SQLOLEDB','Data Source=\u8fdc\u7a0bip;User ID=sa;Password=\u5bc6\u7801').\u5e93\u540d.dbo.\u8868\u540d \r\n\r\n13 \u6570\u636e\u5e93\u91cc\u67091,2,3,4,5 \u51715\u6761\u8bb0\u5f55,\u8981\u7528\u4e00\u6761sql\u8bed\u53e5\u8ba9\u5176\u6392\u5e8f,\u4f7f\u5b83\u6392\u5217\u62104,5,1,2,3,\u600e\u4e48\u5199?\r\n\r\nselect * from t\r\n order by case id when 4 then 1\r\n                  when 5 then 2\r\n                  when 1 then 3\r\n                  when 2 then 4\r\n                  when 3 then 5 end \r\n\r\n \u6216\uff1a\r\nselect * from t order by charindex(cast(id as varchar),'45123')<\/pre>\n<pre lang=\"tsql\">14\uff0c\u53d6\u51fa\u4e00\u4e2a\u5e74\u7ea7\u5404\u73ed\u7684\u524d\u4e24\u540d\uff1a\r\nclass? name? score\r\n2\u73ed??? \u5f20\u4e8c??? 400\r\n2\u73ed??? \u5f20\u4e09??? 300\r\n2\u73ed??? \u5f20\u56db??? 200\r\n2\u73ed??? \u5f20\u4e94??? 100\r\n3\u73ed??? \u5218\u4e00??? 500\r\n3\u73ed??? \u5218\u4e8c??? 400\r\n3\u73ed??? \u5218\u4e09??? 300\r\n3\u73ed??? \u5218\u56db??? 200\r\n3\u73ed??? \u5218\u4e94??? 100\r\n\r\nSELECT t.class,<a href=\"http:\/\/t.name\/\">t.name<\/a>,t.socre\r\nFROM tmp t\r\nWHERE EXISTS( SELECT 1 FROM? (SELECT TOP 2 * FROM tmp tm WHERE tm.class = t.class ORDER BY tm.socre DESC )m WHERE? <a href=\"http:\/\/m.name\/\">m.name<\/a> = <a href=\"http:\/\/t.name\/\">t.name<\/a> )\r\nORDER BY t.class,t.socre DESC,<a href=\"http:\/\/t.name\/\">t.name<\/a>\r\n--\u5229\u7528\u5b50\u67e5\u8be2\u83b7\u53d6\u4e00\u4e2a\u73ed\u7ea7\u5185\u6700\u9ad8\u7684\u4e24\u540d\u7136\u540e\u4e0e\u603b\u8868\u8fde\u63a5\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>1\u3001\u8bf4\u660e\uff1a\u590d\u5236\u8868(\u53ea\u590d\u5236\u7ed3\u6784,\u6e90\u8868\u540d\uff1aa \u65b0\u8868\u540d\uff1ab) (Access\u53ef\u7528) \u6cd5\u4e00\uff1aselect * into [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-545","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/545","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=545"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/545\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=545"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=545"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=545"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}