{"id":1662,"date":"2011-04-02T00:56:22","date_gmt":"2011-04-02T00:56:22","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=1662"},"modified":"2011-04-02T00:59:55","modified_gmt":"2011-04-02T00:59:55","slug":"%e6%95%b0%e5%ad%97%e8%be%85%e5%8a%a9%e8%a1%a8%e8%bf%9e%e7%bb%ad%e8%8c%83%e5%9b%b4%e7%9a%84%e7%bb%93%e6%9e%9c%e5%a4%84%e7%90%86","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2011\/04\/02\/%e6%95%b0%e5%ad%97%e8%be%85%e5%8a%a9%e8%a1%a8%e8%bf%9e%e7%bb%ad%e8%8c%83%e5%9b%b4%e7%9a%84%e7%bb%93%e6%9e%9c%e5%a4%84%e7%90%86\/","title":{"rendered":"\u6570\u5b57\u8f85\u52a9\u8868:\u8fde\u7eed\u8303\u56f4\u7684\u7ed3\u679c\u5904\u7406"},"content":{"rendered":"<pre escaped=\"true\" lang=\"tsql\">\r\n\u6570\u5b57\u8f85\u52a9\u8868\uff1a\u4e00\u4e2a1\u5230N\u7684\u8868 \u5e94\u7528\uff1a\u8fde\u7eed\u8303\u56f4\u7684\u7ed3\u679c\u5904\u7406\uff0c\u5229\u7528group by\u4f1a\u6709\u7f3a\u5931\u8303\u56f4\uff01\r\n\r\n\u521b\u5efa\u6b64\u51fd\u6570\uff0c\u6bcf\u6b21\u8c03\u7528 select dbo.fn_nums(24)\u5373\u53ef\uff1a\r\nCREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE\r\nAS\r\nRETURN\r\n  WITH\r\n  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),\r\n  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),\r\n  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),\r\n  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),\r\n  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),\r\n  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),\r\n  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)\r\n  SELECT n FROM Nums WHERE n &lt;= @n;\r\n--\u6ce8\u5bf9\u4e8en\u7ea7\uff0c\u6700\u5927\u7684\u884c\u6570\u4e3a2\u7684\uff082\u7684n\u6b21\u5e42\uff09\u6b21\u5e42 \u5982n=5\u65f6\uff0c\u6700\u5927\u884c\u6570\u4e3a\uff1a2\u768432\u6b21\u65b9\u51714294967296\u884c\u6570\u636e\uff01\r\nGO\r\n\r\n\u5e94\u7528\uff1a1\uff0c\u4e00\u6bb5\u65e5\u671f\u8303\u56f4\u5185\u7684\u8ba2\u5355\u6570\uff0c\u53ef\u4ee5\u7528group by\uff0c\u4f46\u5f97\u4e0d\u5230\u8fde\u7eed\u7684\u65e5\u671f\uff0c\u5373\u82e5\u67d0\u5929\u6ca1\u6709\u4e1a\u7ee9\uff0c\u5c31\u663e\u793a\u4e0d\u51fa\u6765\uff0c\u6240\u4ee5\u7528group by\u6709\u7f3a\u9677\r\n\u6570\u5b57\u8f85\u52a9\u8868\uff1a\r\nDECLARE @OrderDateBegin VARCHAR(20),@OrderDateEnd VARCHAR(20)\r\nSET @OrderDateBegin = '2011-01-01'\r\nSET @OrderDateEnd = '2011-02-01'\r\nDECLARE @Span INT\r\nSET @Span = DATEDIFF(dd,@OrderDateBegin,@OrderDateEnd)\r\n--SELECT @Span\r\n\r\nSELECT DATEADD(dd,fn.n-1,@OrderDateBegin),\r\n(SELECT COUNT(1) FROM bdorder b(NOLOCK)\r\n WHERE b.OrderDate&gt;=DATEADD(dd,fn.n-1,@OrderDateBegin)\r\n       AND b.OrderDate &lt; DATEADD(dd,fn.n,@OrderDateBegin) )\r\nFROM dbo.fn_nums(@Span) fn\r\n\r\n2\uff0c\u4e0e\u4e0a\u9762\u7c7b\u4f3c\uff0c\u4f46\u67e5\u8be2\u4e00\u5929\u6bcf\u4e2a\u65f6\u95f4\u6bb5\u7684\u6570\u636e\u65f6\u6216\u6bcf\u51e0\u4e2a\u65f6\u95f4\u6bb5\u7684\u6570\u636e\u65f6\uff0c\u4e5f\u53ef\u901a\u8fc7\u4e0a\u9762\u7684\u65b9\u6cd5\u5904\u7406\uff01<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u6570\u5b57\u8f85\u52a9\u8868\uff1a\u4e00\u4e2a1\u5230N\u7684\u8868 \u5e94\u7528\uff1a\u8fde\u7eed\u8303\u56f4\u7684\u7ed3\u679c\u5904\u7406\uff0c\u5229\u7528group by\u4f1a\u6709\u7f3a\u5931\u8303\u56f4\uff01 \u521b\u5efa\u6b64\u51fd\u6570\uff0c\u6bcf\u6b21\u8c03\u7528 [&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-1662","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\/1662","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=1662"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1662\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=1662"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=1662"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=1662"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}