常用的SQL数据库语句总结--分页存储过程

1 通过select top进行分页查询  /*通用分页程序(排序为组合字段排序)查询原理：需要查询第N页时，首先取到前N页的所有数据，然后排出前N-1页的数据，就是第N页的数据@sortFields 排序的字段名，只能一个字段,需要具有唯一性；可以采用数据记录的主键
*/create PROCEDURE [dbo].[GetOnePageData](
 @tblName   nvarchar(400),       -- 表或视图名称
 @selectFields nvarchar(4000) = ''*'',  -- 需要返回的字段名
 @strWhere  nvarchar(4000) = null,  -- 查询条件 (注意: 不要加 where)
 @sortFields nvarchar(400) = null,      -- 排序的字段名，只能一个字段,需要具有唯一性；可以采用数据记录的主键
 @pageIndex  int = 1,           -- 页码
 @pageSize   int = 20,          -- 页尺寸
 @descOrderType bit = 1,  -- 设置排序类型, 非 0 值则降序
 @compositeSortFields nvarchar(4000),--组合排序字段
 @doCount  bit = 0,   --是否返回记录总数
 @isPage bit = 1  -- 是否进行分页查询。若不分页，则返回所有的查询结果；若分页，则返回该页数据
)
AS
 declare @strSQL   nvarchar(4000)       -- 主语句
 declare @strTmp   nvarchar(400)        -- 临时变量
 declare @strOrder nvarchar(400)        -- 排序类型
 declare @strCompOrder nvarchar(2000)   -- 组合排序类型
 
 if @pageIndex < 1--处理传进来的页编号
 begin
  Set @pageIndex=1
 end 
 
 if @descOrderType = 1 --如果@descOrderType是1，就执行降序，这句很重要！
  begin
   set @strTmp = ''<(select min''
   if @sortFields is null
    set @strOrder = '' ''
   else
    set @strOrder = '' order by ['' + @sortFields +''] desc''  
  end
 else
  begin
   set @strTmp = ''>(select max''
   if @sortFields is null
    set @strOrder = '' ''
   else
    set @strOrder = '' order by ['' + @sortFields +''] asc''  
  end
 if @compositeSortFields is not null
  begin   
    set @strTmp = '' not in (select ''
    set @strCompOrder =  '' order by  '' + @compositeSortFields +''''
  end
 else
  set @strCompOrder =  @strOrder
 ---返回记录总数
 if @doCount = 1
  begin
   if @strWhere  is not null
    set @strSQL = ''select count(*) as Total from '' + @tblName + '' where ''+@strWhere
   else
    set @strSQL = ''select count(*) as Total from '' + @tblName + ''''  
   exec (@strSQL)
  end
 
 if @isPage = 1 ---要进行分页查询
  begin 
   if @PageIndex = 1--如果是第一页就执行此代码，这样会加快执行速度
    begin
     if @strWhere is not null  
      set @strSQL = ''select top '' + str(@PageSize) +'' ''+@selectFields+ ''  from '' + @tblName + '' where '' + @strWhere + '' '' + @strCompOrder
     else
      set @strSQL = ''select top '' + str(@PageSize) +'' ''+@selectFields+ ''  from ''+ @tblName + '' ''+ @strCompOrder  
    end
   else
    begin
     --以下代码赋予了@strSQL以真正执行的SQL代码
     set @strSQL = ''select top '' + str(@PageSize) +'' ''+@selectFields+ ''  from ''
         + @tblName + '' where ['' + @sortFields + '']'' + @strTmp + ''([''+ @sortFields + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [''+ @sortFields + ''] from '' + @tblName + '''' + @strCompOrder + '') as tblTmp)''+ @strCompOrder
         
     if @strWhere is not null  
      set @strSQL = ''select top '' + str(@PageSize) +'' ''+@selectFields+ ''  from ''
        + @tblName + '' where ['' + @sortFields + '']'' + @strTmp + ''([''
        + @sortFields + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [''
        + @sortFields + ''] from '' + @tblName + '' where '' + @strWhere + '' ''
        + @strCompOrder + '') as tblTmp) and '' + @strWhere + '' '' + @strCompOrder
    end
   exec (@strSQL)
  end
 else ---返回所有结果，不进行分页查询；也不需要执行总数统计
  begin
   if @strWhere is not null  
    set @strSQL = ''select '' + '' ''+@selectFields+ ''  from '' + @tblName + '' where '' + @strWhere + '' '' + @strCompOrder
   else
    set @strSQL = ''select '' + '' ''+@selectFields+ ''  from ''+ @tblName + '' ''+ @strCompOrder  
   exec (@strSQL)
  end 
  
 RETURN2通过系统存储过程进行分页查询 /*
一共返回三个表第一个表为空 查询字符串的条件中有 like ,in 可能出现问题，具体出现原因不明
，第二个表包含总页数，总行数，当前页
第三个表包含查询记录*/
create   procedure [dbo].[GetOnePageData]
@sqlstr nvarchar(4000), --查询字符串  ，就是一般的查询语句，不需要top关键字
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int--
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output--定义与游标和游标选项相关联的 SQL 语句，然后填充游标。
--选择总页数，总行数，当前页
select ceiling(1.0*@rowcount/@pagesize) as TotalPages,@rowcount as TotalRows,@currentpage as CurPage
set @currentpage=(@currentpage-1)*@pagesize+1
--查询记录
exec sp_cursorfetch @P1,16,@currentpage,@pagesize --从游标中提取一行或一个行块。
--返回值
exec sp_cursorclose @P1--关闭并释放游标
set nocount off
go3 通过新函数ROW_NUMBER()进行分页查询/*
只适用与sql2005，据调查此方法限制最少，性能最佳
@strOrder nvarchar(400)--需要加 order by ，至少一个字段，是分页的字段
返回两个表，第一个表包含总行数，第二个表是查询到的记录
--分页查询的原理：
--1.先将预分页内容按照排序条件加上自增列导入到临时表中（或表变量）
--2.针对临时表操作，找到相应的N页对应的自增列编码范围
--3.根据第N页对应的自增列编码范围，查找第N页内容
 
--需要注意的是：
--第一是添加自增列，确定行号
--第二缩减中间处理过程的操作数据量*/
create  PROCEDURE GetDataListWithPage
(
    @tblName   nvarchar(400),       -- 表或视图名称 可以是多个表或视图名称
 @selectFields nvarchar(400) = ''*'',  -- 需要返回的字段名 可以是多个字段名
 @strWhere  nvarchar(400) = null,  -- 查询条件 (注意: 不要加 where) 
 @pageIndex  int = 1,           -- 页码
 @pageSize   int = 20,          -- 页尺寸
    @strOrder nvarchar(400)--需要加 order by 可以是多个字段名,至少一个字段
)
as
begin
    declare @strSQL   nvarchar(4000)       -- 主语句 
    declare @strTotal   nvarchar(400)        -- 计算总行数语句
 if @pageIndex < 1
 begin
  Set @pageIndex=1
 end 
       set @strSQL='' select ROW_NUMBER() OVER( '' + @strOrder+'' ) as RowNumID, '' +@selectFields+'' into #temp ''+'' from  '' +@tblName
       set @strTotal = '' select count(*) as Total from '' + @tblName + ''''
      if (@strWhere  is  null or @strWhere='''')
            begin           
            set @strSQL=@strSQL+''  select  ''+@selectFields +''  from #temp  where RowNumID>''+str((@PageIndex-1)*@PageSize)
         +'' and RowNumID<=  '' +str(@PageIndex*@PageSize)
          end
      else
         begin
         set @strSQL=@strSQL+''  where '' +@strWhere+
        ''  select  ''+@selectFields +''  from #temp  where RowNumID>''+str((@PageIndex-1)*@PageSize)
         +'' and RowNumID<=  '' +str(@PageIndex*@PageSize)
          set @strTotal = @strTotal + '' where ''+@strWhere
         end       
    EXEC dbo.sp_executesql @strTotal--总记录数
    EXEC dbo.sp_executesql @strSQL--第N页数据
end
go
  综述，在数据库拼字符串性能不是特别好，如果需要更好的性能，可以把第一或者第三种分页方法根据实际情况，进行改写，改为处理特定表的分页存储过程，不具有通用性，但性能会好很多。这或许就是通用和性能之间的矛盾吧。