{"id":8913,"date":"2014-05-26T12:46:30","date_gmt":"2014-05-26T04:46:30","guid":{"rendered":"http:\/\/bohu.net\/blog\/?p=8913"},"modified":"2014-05-26T12:46:30","modified_gmt":"2014-05-26T04:46:30","slug":"%e4%ba%9b%e8%ae%b8%e4%bc%9a%e5%b8%b8%e7%94%a8%e5%88%b0%e7%9a%84sql%e8%af%ad%e5%8f%a5","status":"publish","type":"post","link":"https:\/\/www.bohu.net\/blog\/8913\/","title":{"rendered":"\u4e9b\u8bb8\u4f1a\u5e38\u7528\u5230\u7684sql\u8bed\u53e5"},"content":{"rendered":"<p>\u8fd9\u91cc\u8bb0\u5f55\u4e9b\u8bb8\u4e2a\u4f1a\u7ecf\u5e38\u7528\u5230\u7684sql\u8bed\u53e5\u3002<\/p>\n<p>&nbsp;<\/p>\n<p>P.S.\u00a0 \u00a0[Your_Table]\u00a0 = \u4f60\u7684\u8868\u540d, [nid] = \u4f60\u8868\u4e2d\u7684\u4e3b\u952e\u552f\u4e00\u5217.<\/p>\n<pre class=\"brush:sql\">\/*\u6309\u5206\u9875\u6570\u91cf\u53d6\u8868\u8bb0\u5f55\u4e2d\u7684\u6570\u636e*\/\n\/*\u4ee5nid\u6392\u5e8f, \u53d620\u6761\u4e4b\u540e\u768410\u6761*\/SELECT TOP 10 *\nFROM Your_Table\nWHERE (nid NOT IN\n          (SELECT TOP 20 nid\n         FROM Your_Table\n         ORDER BY nid))\nORDER BY nid\n<\/pre>\n<pre class=\"brush:sql\">\/*\u53d6\u4f20\u5165\u6761\u4ef6\u8bb0\u5f55\u7684\u524d\u4e00\u6761\u6570\u636e*\/\n\/*\u53d6 nid='533' \u8bb0\u5f55\u7684\u524d\u4e00\u6761, \u8fd4\u56de\u8bb0\u5f55 nid='532'*\/\nSELECT *\nFROM Your_Table\nWHERE (nid =\n          (SELECT MAX(nid)\n         FROM Your_Table\n         WHERE nid &lt; 533))\n<\/pre>\n<pre class=\"brush:sql\">\/*\u53d6\u4f20\u5165\u6761\u4ef6\u8bb0\u5f55\u7684\u540e\u4e00\u6761\u6570\u636e*\/\n\/*\u53d6 nid='533' \u8bb0\u5f55\u7684\u540e\u4e00\u6761, \u8fd4\u56de\u8bb0\u5f55 nid='532'*\/\t\t \nSELECT *\nFROM Your_Table\nWHERE (nid =\n          (SELECT MIN(nid)\n         FROM Your_Table\n         WHERE nid &gt; 534))\n\t\t \n\t\t \n<\/pre>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"text-align: left; color: #000000; text-transform: none; line-height: 21px; text-indent: 0px; letter-spacing: normal; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 14px; font-style: normal; font-variant: normal; word-spacing: 0px; float: none; display: inline !important; white-space: normal; font-size-adjust: none; font-stretch: normal; background-color: #ffffff; -webkit-text-stroke-width: 0px;\">\u5206\u9875\u5b58\u50a8\u8fc7\u7a0b\uff0cSQL 2000 \u6570\u636e\u5e93\u4e0b\u4f7f\u7528\u7684\uff1a<\/span><\/strong><\/p>\n<pre class=\"brush:sql\">-- =============================================\n\n-- DocumentName       \u5206\u9875\u5b58\u50a8\u8fc7\u7a0b <span style=\"font: 14px\/21px Verdana, Arial, Helvetica, sans-serif; text-align: left; color: #000000; text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; float: none; display: inline !important; white-space: normal; background-color: #ffffff; -webkit-text-stroke-width: 0px;\">SQL 2000\u6570\u636e\u5e93\u4e0b\u4f7f\u7528\u7684<\/span>\n\n-- \u63cf\u8ff0               \u5229\u7528SQL\u67e5\u8be2\u8bed\u53e5\u8fdb\u884c\u5206\u9875\n\n-- \u8f93\u5165\n\n--       {\n\n--        @SQL            :    SQL\u67e5\u8be2\u8bed\u53e5,\u793a\u4f8b\uff1a'Select * from [TableName]'\n\n--        @Order        :    \u6392\u5e8f,\u793a\u4f8b\uff1a[ColumnName] [ASC | DESC]\n\n--        @CurPage        :    \u5f53\u524d\u9875,\u793a\u4f8b\uff1a0..9\n\n--        @PageRows        :    \u6bcf\u9875\u663e\u793a\u7684\u884c\u6570,\u793a\u4f8b\uff1a0..9\n\n--        @TotalRecorder:    \u67e5\u8be2\u8bb0\u5f55\u603b\u6570(\u8f93\u51fa\u53c2\u6570)\n\n--          @IsXML        :    \u8868\u793a\u8fd4\u56de\u7684\u7ed3\u679c\uff0c0\u8868\u793a\u4ee5\u8868\u683c\u5f62\u5f0f\u8bb0\u5f55\uff0c1\u8868\u793a\u4ee5XML\u683c\u5f0f\u8fd4\u56de\u8bb0\u5f55\uff1b\u9ed8\u8ba4\u4e3a0\n\n--       }\n\n-- =============================================\n\n\n\nALTER PROCEDURE [dbo].[SeparatePage]\n\n    -- Add the parameters for the stored procedure here\n\n    @SQL Nvarchar(2000),\n\n    @Order Nvarchar(20),\n\n    @CurPage int,\n\n    @PageRows int,\n\n    @TotalRecorder int output,\n\n    @IsXML bit = 0\n\nAS\n\nBEGIN\n\n    -- SET NOCOUNT ON added to prevent extra result sets from\n\n    SET NOCOUNT ON;\n\n    declare @ExceSQL nvarchar(4000)\n\n    \n\n    --\u8bbe\u7f6e\u5f00\u59cb\u884c\u53f7\n\n    declare  @start_row_num AS int\n\n    SET @start_row_num = (@CurPage - 1) * @PageRows\n\n   \n\n    if @CurPage &gt; 1 \n\n        BEGIN\n\n            SET @start_row_num = @start_row_num + 1;\n\n            SET @PageRows = @PageRows - 1\n\n        END\n\n    else\n\n        SET @start_row_num = @start_row_num\n\n    \n\n    --\u83b7\u53d6\u603b\u8bb0\u5f55\u6570\n\n    set @ExceSQL = 'SELECT tb.* into [tb_Temp] FROM ('+ @SQL +') tb ORDER BY'+ @order\n\n    execute(@ExceSQL)\n\n    SELECT @TotalRecorder = COUNT(*) from [tb_Temp]\n\n\n\n    ALTER TABLE [tb_Temp] add [RowNumber] int\n\n\n\n    --\u8bbe\u7f6e\u67e5\u8be2\u8bed\u53e5\n\n    SET @ExceSQL = 'SELECT TOP '+ convert(varchar(10),@PageRows) +' * \n\n                    FROM (SELECT TOP '+ convert(varchar(10),@PageRows) +' * \n\n                    FROM (SELECT TOP '+ convert(varchar(10),@TotalRecorder-@start_row_num+1) +' *\n\n                    FROM [tb_Temp] ORDER BY '+ REPLACE(@Order,'DESC','ASC') +') AS a\n\n                    ORDER BY '+ REPLACE(@Order,'ASC','DESC') +') AS b'\n\n\n\n    IF(@IsXML = 1)\n\n        SET @ExceSQL = @ExceSQL + ' FOR XML AUTO,ELEMENTS'\n\n \n\n    EXECUTE(@ExceSQL)\n\n    DROP Table [tb_Temp]\n\nEND<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-align: left; color: #000000; text-transform: none; line-height: 21px; text-indent: 0px; letter-spacing: normal; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 14px; font-style: normal; font-variant: normal; word-spacing: 0px; float: none; display: inline !important; white-space: normal; font-size-adjust: none; font-stretch: normal; background-color: #ffffff; -webkit-text-stroke-width: 0px;\"><strong>\u5206\u9875\u5b58\u50a8\u8fc7\u7a0b\uff0cSQL 2005 \u6570\u636e\u5e93\u4e0b\u4f7f\u7528\u7684\uff1a<\/strong><\/span><\/p>\n<pre class=\"brush:sql\">set ANSI_NULLS ON\n\nset QUOTED_IDENTIFIER ON\n\ngo\n\n\n-- =============================================\n\n-- DocumentName       \u5206\u9875\u5b58\u50a8\u8fc7\u7a0b  <span style=\"font: 14px\/21px Verdana, Arial, Helvetica, sans-serif; text-align: left; color: #000000; text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; float: none; display: inline !important; white-space: normal; background-color: #ffffff; -webkit-text-stroke-width: 0px;\">SQL 2005\u6570\u636e\u5e93\u4e0b\u4f7f\u7528<\/span>\n\n-- \u63cf\u8ff0                 \u5229\u7528SQL\u67e5\u8be2\u8bed\u53e5\u8fdb\u884c\u5206\u9875\n\n-- \u8f93\u5165\n\n--       {\n\n--        @SQL            :    SQL\u67e5\u8be2\u8bed\u53e5,\u793a\u4f8b\uff1a'Select * from [TableName]'\n\n--        @Order        :    \u6392\u5e8f,\u793a\u4f8b\uff1a[ColumnName] [ASC | DESC]\n\n--        @CurPage        :    \u5f53\u524d\u9875,\u793a\u4f8b\uff1a0..9\n\n--        @PageRows        :    \u6bcf\u9875\u663e\u793a\u7684\u884c\u6570,\u793a\u4f8b\uff1a0..9\n\n--        @TotalRecorder:    \u67e5\u8be2\u8bb0\u5f55\u603b\u6570(\u8f93\u51fa\u53c2\u6570)\n\n--          @IsXML        :    \u8868\u793a\u8fd4\u56de\u7684\u7ed3\u679c\uff0c0\u8868\u793a\u4ee5\u8868\u683c\u5f62\u5f0f\u8bb0\u5f55\uff0c1\u8868\u793a\u4ee5XML\u683c\u5f0f\u8fd4\u56de\u8bb0\u5f55\uff1b\u9ed8\u8ba4\u4e3a0\n\n--       }\n\n-- =============================================\n\n\n\nALTER PROCEDURE [dbo].[SeparatePage]\n\n    -- Add the parameters for the stored procedure here\n\n    @SQL Nvarchar(2000),\n\n    @Order Nvarchar(20),\n\n    @CurPage int,\n\n    @PageRows int,\n\n    @TotalRecorder int output,\n\n    @IsXML bit = 0\n\nAS\n\nBEGIN\n\n    -- SET NOCOUNT ON added to prevent extra result sets from\n\n    SET NOCOUNT ON;\n\n    declare @ExceSQL nvarchar(4000)\n\n    \n\n    --\u8bbe\u7f6e\u5f00\u59cb\u884c\u53f7\n\n    declare  @start_row_num AS int\n\n    SET @start_row_num = (@CurPage - 1) * @PageRows\n\n   \n\n    if @CurPage &gt; 1 \n\n        BEGIN\n\n            SET @start_row_num = @start_row_num + 1;\n\n            SET @PageRows = @PageRows - 1\n\n        END\n\n    else\n\n        SET @start_row_num = @start_row_num\n\n    \n\n    --\u8bbe\u7f6e\u6807\u7b7e\u8bed\u53e5\n\n    declare @RowNumber nvarchar(100)\n\n    set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order + ') as RowNumber from '\n\n\n\n    set @SQL = Replace(@SQL,' from ',@RowNumber)\n\n\n\n    --\u83b7\u53d6\u603b\u8bb0\u5f55\u6570\n\n    set @ExceSQL = 'WITH [TempTable] AS (' + @SQL + ') \n\n        select @TotalRecorder=max(RowNumber) from [TempTable]'\n\n\n\n    execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output\n\n\n\n    --\u8bbe\u7f6e\u67e5\u8be2\u8bed\u53e5\n\n    set @ExceSQL = 'WITH [TempTable] AS (' + @SQL + ') \n\n        select * from [TempTable] where RowNumber between ' + Convert(nvarchar,@start_row_num)\n\n        + ' And ' + Convert(nvarchar,@start_row_num+@PageRows)\n\n    \n\n    IF(@IsXML = 1)SET @ExceSQL = @ExceSQL + 'FOR XML AUTO,ELEMENTS'\n\n \n\n    execute(@ExceSQL)\n\n\n\nEND<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u8fd9\u91cc\u8bb0\u5f55\u4e9b\u8bb8\u4e2a\u4f1a\u7ecf\u5e38\u7528\u5230\u7684sql\u8bed\u53e5\u3002 &nbsp; P.S.\u00a0 \u00a0[Your_Table]\u00a0 = \u4f60\u7684\u8868\u540d, &hellip; <a href=\"https:\/\/www.bohu.net\/blog\/8913\/\" class=\"more-link\">\u7ee7\u7eed\u9605\u8bfb<span class=\"screen-reader-text\">\u201c\u4e9b\u8bb8\u4f1a\u5e38\u7528\u5230\u7684sql\u8bed\u53e5\u201d<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,9,26],"tags":[358,359,512],"class_list":["post-8913","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server-sql","category-26","tag-sql-2","tag-sql-server","tag-512"],"_links":{"self":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/posts\/8913","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/comments?post=8913"}],"version-history":[{"count":0,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/posts\/8913\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/media?parent=8913"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/categories?post=8913"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/tags?post=8913"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}