{"id":2354,"date":"2012-11-17T14:20:08","date_gmt":"2012-11-17T14:20:08","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2354"},"modified":"2012-11-17T14:37:08","modified_gmt":"2012-11-17T14:37:08","slug":"%e6%9b%b4%e5%bf%ab%e7%9a%84distinct","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/11\/17\/%e6%9b%b4%e5%bf%ab%e7%9a%84distinct\/","title":{"rendered":"\u66f4\u5feb\u7684distinct"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\nUSE     tempdb;\r\nGO\r\nDROP    TABLE dbo.Test;\r\nGO\r\nCREATE  TABLE \r\n        dbo.Test \r\n        (\r\n        data            INTEGER NOT NULL,\r\n        );\r\nGO\r\nCREATE  CLUSTERED INDEX c ON dbo.Test (data);\r\nGO\r\n-- Lots of duplicated values\r\nINSERT  dbo.Test WITH (TABLOCK)\r\n        (data)\r\nSELECT  TOP (5000000)\r\n        ROW_NUMBER() OVER (ORDER BY (SELECT 0)) \/ 117329\r\nFROM    master.sys.columns C1,\r\n        master.sys.columns C2,\r\n        master.sys.columns C3;\r\nGO\r\n\r\nSET     STATISTICS TIME ON;\r\n\r\n-- 1591ms CPU\r\nSELECT  DISTINCT \r\n        data\r\nFROM    dbo.Test;\r\n\r\nSQL Server \u5206\u6790\u548c\u7f16\u8bd1\u65f6\u95f4: \r\n   CPU \u65f6\u95f4 = 859 \u6beb\u79d2\uff0c\u5360\u7528\u65f6\u95f4 = 2702 \u6beb\u79d2\u3002\r\n\r\nSQL Server \u6267\u884c\u65f6\u95f4:\r\n   CPU \u65f6\u95f4 = 0 \u6beb\u79d2\uff0c\u5360\u7528\u65f6\u95f4 = 0 \u6beb\u79d2\u3002\r\n\r\n(43 \u884c\u53d7\u5f71\u54cd)\r\n\r\nSQL Server \u6267\u884c\u65f6\u95f4:\r\n   CPU \u65f6\u95f4 = 967 \u6beb\u79d2\uff0c\u5360\u7528\u65f6\u95f4 = 5881 \u6beb\u79d2\u3002\r\n   \r\n-- 15ms CPU\r\nWITH    RecursiveCTE\r\nAS      (\r\n        SELECT  data = MIN(T.data)\r\n        FROM    dbo.Test T\r\n        UNION   ALL\r\n        SELECT  R.data\r\n        FROM    (\r\n                -- A cunning way to use TOP in the recursive part of a CTE :)\r\n                SELECT  T.data,\r\n                        rn = ROW_NUMBER() OVER (ORDER BY T.data)\r\n                FROM    dbo.Test T\r\n                JOIN    RecursiveCTE R\r\n                        ON  R.data &lt; T.data\r\n                ) R\r\n        WHERE   R.rn = 1\r\n        )\r\nSELECT  *\r\nFROM    RecursiveCTE\r\n\r\n--OPTION  (MAXRECURSION 0);\r\n\r\n\u5728\u6709\u7d22\u5f15\u4e14\u6392\u597d\u5e8f\u7684\u60c5\u51b5\u4e0b\uff0c\u540e\u8005\u901f\u5ea6\u66f4\u5feb\uff01\r\n\r\nSET     STATISTICS TIME OFF;\r\nGO\r\nDROP    TABLE dbo.Test;\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>USE tempdb; GO DROP TABLE dbo.Test; GO CREATE TABLE dbo [&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-2354","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\/2354","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=2354"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2354\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2354"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2354"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2354"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}