{"id":665,"date":"2011-01-28T09:38:20","date_gmt":"2011-01-28T01:38:20","guid":{"rendered":""},"modified":"2014-03-11T22:35:15","modified_gmt":"2014-03-11T14:35:15","slug":"sql%e5%b8%b8%e7%94%a8%e5%88%86%e9%a1%b5%e7%9a%84%e5%8a%9e%e6%b3%95","status":"publish","type":"post","link":"http:\/\/www.xiaoyebailong.com\/index.php\/2011\/01\/28\/665.htm","title":{"rendered":"SQL\u5e38\u7528\u5206\u9875\u7684\u529e\u6cd5"},"content":{"rendered":"

\u8868\u4e2d\u4e3b\u952e\u5fc5\u987b\u4e3a\u6807\u8bc6\u5217\uff0c[ID] int IDENTITY (1,1) <\/p>\n

1.\u5206\u9875\u65b9\u6848\u4e00\uff1a(\u5229\u7528Not In\u548cSELECT TOP\u5206\u9875) <\/p>\n

\u8bed\u53e5\u5f62\u5f0f\uff1a
\nSELECT TOP \u9875\u8bb0\u5f55\u6570\u91cf *
\nFROM \u8868\u540d
\nWHERE (ID NOT IN
\n(SELECT TOP (\u6bcf\u9875\u884c\u6570*(\u9875\u6570-1)) ID
\nFROM \u8868\u540d
\nORDER BY ID))
\nORDER BY ID
\n\/\/\u81ea\u5df1\u8fd8\u53ef\u4ee5\u52a0\u4e0a\u4e00\u4e9b\u67e5\u8be2\u6761\u4ef6 <\/p>\n

\n\u4f8b:
\nselect top 2 *
\nfrom Sys_Material_Type
\nwhere (MT_ID not in
\n(select top (2*(3-1)) MT_ID from Sys_Material_Type order by MT_ID))
\norder by MT_ID <\/p>\n

2.\u5206\u9875\u65b9\u6848\u4e8c\uff1a(\u5229\u7528ID\u5927\u4e8e\u591a\u5c11\u548cSELECT TOP\u5206\u9875\uff09 <\/p>\n

\u8bed\u53e5\u5f62\u5f0f\uff1a
\nSELECT TOP \u6bcf\u9875\u8bb0\u5f55\u6570\u91cf *
\nFROM \u8868\u540d
\nWHERE (ID >
\n(SELECT MAX(id)
\nFROM (SELECT TOP \u6bcf\u9875\u884c\u6570*\u9875\u6570 id FROM \u8868
\nORDER BY id) AS T)
\n)
\nORDER BY ID <\/p>\n

\u4f8b:
\nSELECT TOP 2 *
\nFROM Sys_Material_Type
\nWHERE (MT_ID >
\n(SELECT MAX(MT_ID)
\nFROM (SELECT TOP (2*(3-1)) MT_ID
\nFROM Sys_Material_Type
\nORDER BY MT_ID) AS T))
\nORDER BY MT_ID <\/p>\n

3.\u5206\u9875\u65b9\u6848\u4e09\uff1a(\u5229\u7528SQL\u7684\u6e38\u6807\u5b58\u50a8\u8fc7\u7a0b\u5206\u9875)
\ncreate procedure SqlPager
\n@sqlstr nvarchar(4000), –\u67e5\u8be2\u5b57\u7b26\u4e32
\n@currentpage int, –\u7b2cN\u9875
\n@pagesize int –\u6bcf\u9875\u884c\u6570
\nas
\nset nocount on
\ndeclare @P1 int, –P1\u662f\u6e38\u6807\u7684id
\n@rowcount int
\nexec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
\nselect ceiling(1.0*@rowcount\/@pagesize) as \u603b\u9875\u6570–,@rowcount as \u603b\u884c\u6570,@currentpage as \u5f53\u524d\u9875
\nset @currentpage=(@currentpage-1)*@pagesize+1
\nexec sp_cursorfetch @P1,16,@currentpage,@pagesize
\nexec sp_cursorclose @P1
\nset nocount off <\/p>\n

4.\u603b\u7ed3:
\n\u5176\u5b83\u7684\u65b9\u6848\uff1a\u5982\u679c\u6ca1\u6709\u4e3b\u952e\uff0c\u53ef\u4ee5\u7528\u4e34\u65f6\u8868\uff0c\u4e5f\u53ef\u4ee5\u7528\u65b9\u6848\u4e09\u505a\uff0c\u4f46\u662f\u6548\u7387\u4f1a\u4f4e\u3002
\n\u5efa\u8bae\u4f18\u5316\u7684\u65f6\u5019\uff0c\u52a0\u4e0a\u4e3b\u952e\u548c\u7d22\u5f15\uff0c\u67e5\u8be2\u6548\u7387\u4f1a\u63d0\u9ad8\u3002 <\/p>\n

\u901a\u8fc7SQL \u67e5\u8be2\u5206\u6790\u5668\uff0c\u663e\u793a\u6bd4\u8f83\uff1a\u6211\u7684\u7ed3\u8bba\u662f:
\n\u5206\u9875\u65b9\u6848\u4e8c\uff1a(\u5229\u7528ID\u5927\u4e8e\u591a\u5c11\u548cSELECT TOP\u5206\u9875\uff09\u6548\u7387\u6700\u9ad8\uff0c\u9700\u8981\u62fc\u63a5SQL\u8bed\u53e5
\n\u5206\u9875\u65b9\u6848\u4e00\uff1a(\u5229\u7528Not In\u548cSELECT TOP\u5206\u9875) \u6548\u7387\u6b21\u4e4b\uff0c\u9700\u8981\u62fc\u63a5SQL\u8bed\u53e5
\n\u5206\u9875\u65b9\u6848\u4e09\uff1a(\u5229\u7528SQL\u7684\u6e38\u6807\u5b58\u50a8\u8fc7\u7a0b\u5206\u9875) \u6548\u7387\u6700\u5dee\uff0c\u4f46\u662f\u6700\u4e3a\u901a\u7528 <\/p>\n

 <\/p>\n

\u9488\u5bf9SQL2005\u7248\u672c\u7684\uff1a
\n <\/p>\n

\n <\/p>\n

<\/p>\n
SQL code \n<\/dt>\n
\n

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[up_Page2005] 
 @TableName varchar(50),        --\u8868\u540d
 @Fields varchar(5000) = '*',    --\u5b57\u6bb5\u540d(\u5168\u90e8\u5b57\u6bb5\u4e3a*)
 @OrderField varchar(5000),        --\u6392\u5e8f\u5b57\u6bb5(\u5fc5\u987b!\u652f\u6301\u591a\u5b57\u6bb5)
 @sqlWhere varchar(5000) = Null,--\u6761\u4ef6\u8bed\u53e5(\u4e0d\u7528\u52a0where)
 @pageSize int,                    --\u6bcf\u9875\u591a\u5c11\u6761\u8bb0\u5f55
 @pageIndex int = 1 ,            --\u6307\u5b9a\u5f53\u524d\u4e3a\u7b2c\u51e0\u9875
 @TotalPage int output            --\u8fd4\u56de\u603b\u9875\u6570 
as
begin

    Begin Tran --\u5f00\u59cb\u4e8b\u52a1

    Declare @sql nvarchar(4000);
    Declare @totalRecord int;    

    --\u8ba1\u7b97\u603b\u8bb0\u5f55\u6570
         
    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'select @totalRecord = count(*) from ' + @TableName
    else
        set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere

    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--\u8ba1\u7b97\u603b\u8bb0\u5f55\u6570        
    
    --\u8ba1\u7b97\u603b\u9875\u6570
    select @TotalPage=CEILING((@totalRecord+0.0)\/@PageSize)

    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
    else
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' with(nolock) where ' + @SqlWhere    
        

    --\u5904\u7406\u9875\u6570\u8d85\u51fa\u8303\u56f4\u60c5\u51b5
    if @PageIndex<=0 
        Set @pageIndex = 1
    
    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage

     --\u5904\u7406\u5f00\u59cb\u70b9\u548c\u7ed3\u675f\u70b9
    Declare @StartRecord int
    Declare @EndRecord int
    
    set @StartRecord = (@pageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1

    --\u7ee7\u7eed\u5408\u6210sql\u8bed\u53e5
    set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
     print @sql   
    Exec(@Sql)
    ---------------------------------------------------
    If @@Error <> 0
      Begin
        RollBack Tran
        Return -1
      End
     Else
      Begin
        Commit Tran
        Return @totalRecord ---\u8fd4\u56de\u8bb0\u5f55\u603b\u6570
      End    
end
 <\/div><\/pre>\n<\/dd>\n<\/dl>\n","protected":false},"excerpt":{"rendered":"
\u8868\u4e2d\u4e3b\u952e\u5fc5\u987b\u4e3a\u6807\u8bc6\u5217\uff0c[ID] int IDENTITY (1,1) 1.\u5206\u9875\u65b9\u6848\u4e00\uff1a(\u5229\u7528Not In\u548cSELECT TOP\u5206\u9875) \u8bed\u53e5\u5f62\u5f0f\uff1a SELECT TOP \u9875\u8bb0\u5f55\u6570\u91cf * FROM \u8868\u540d WHERE (ID NOT IN (SE…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"topic":[],"class_list":["post-665","post","type-post","status-publish","format-standard","hentry","category-technology"],"_links":{"self":[{"href":"http:\/\/www.xiaoyebailong.com\/index.php\/wp-json\/wp\/v2\/posts\/665","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.xiaoyebailong.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.xiaoyebailong.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.xiaoyebailong.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.xiaoyebailong.com\/index.php\/wp-json\/wp\/v2\/comments?post=665"}],"version-history":[{"count":1,"href":"http:\/\/www.xiaoyebailong.com\/index.php\/wp-json\/wp\/v2\/posts\/665\/revisions"}],"predecessor-version":[{"id":66502,"href":"http:\/\/www.xiaoyebailong.com\/index.php\/wp-json\/wp\/v2\/posts\/665\/revisions\/66502"}],"wp:attachment":[{"href":"http:\/\/www.xiaoyebailong.com\/index.php\/wp-json\/wp\/v2\/media?parent=665"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.xiaoyebailong.com\/index.php\/wp-json\/wp\/v2\/categories?post=665"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.xiaoyebailong.com\/index.php\/wp-json\/wp\/v2\/tags?post=665"},{"taxonomy":"topic","embeddable":true,"href":"http:\/\/www.xiaoyebailong.com\/index.php\/wp-json\/wp\/v2\/topic?post=665"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}