海量数据分页查询

SELECT TOP 25 Id,RegisterId,FileNameId,CreateTime FROM tbFileNameRecord WHERE Id < ( SELECT MIN ( Id ) FROM ( SELECT TOP 100 Id FROM tbFileNameRecord WHERE RegisterId in (123,456,789) ORDER BY Id DESC ) AS tbTMP ) AND RegisterId in (123,456,789) ORDER BY Id DESC

SELECT TOP 25 Id,RegisterId,FileNameId,CreateTime FROM tbFileNameRecord WHERE Id > ( SELECT MAX ( Id ) FROM ( SELECT TOP 100 Id FROM tbFileNameRecord WHERE RegisterId in (123,456,789) ORDER BY Id ) AS tbTMP ) AND RegisterId in (123,456,789) ORDER BY Id

--------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[procedure_search_record]
 @tableName VARCHAR(256),    -- 表名
 @returnFields VARCHAR(1024) = '*',  -- 需要返回的列
 @orderFields VARCHAR(256)='',   -- 排序的字段名
 @pageSize INT = 25,      -- 页尺寸
 @pageIndex INT = 1,      -- 页码
 @returnCount BIT = 0,     -- 返回记录总数, 非0值则返回
 @orderType BIT = 0,      -- 设置排序类型, 非0值则降序
 @whereFields VARCHAR(1024) = ''   -- 查询条件 (注意: 不要加 WHERE)
AS
 DECLARE @SQL VARCHAR(4096)     -- 主语句
 DECLARE @TMP VARCHAR(1024)     -- 临时变量
 DECLARE @ORD VARCHAR(512)     -- 排序类型

 IF (@returnCount <> 0)
 BEGIN
  IF (@whereFields <> '')
  BEGIN
   SET @SQL = 'SELECT COUNT(*) AS Total FROM ' + @tableName + ' WHERE ' + @whereFields
  END ELSE
   SET @SQL = 'SELECT COUNT(*) AS Total FROM ' + @tableName
  END
 ELSE BEGIN
  IF (@orderType <> 0)
  BEGIN
   SET @TMP = '< ( SELECT MIN'
   SET @ORD = ' ORDER BY ' + @orderFields +' DESC'
  END ELSE BEGIN
   SET @TMP = '> ( SELECT MAX'
   SET @ORD = ' ORDER BY ' + @orderFields
  END
  IF (@pageIndex = 1)
  BEGIN
   IF (@whereFields <> '')
   BEGIN
    SET @SQL = 'SELECT TOP ' + STR(@pageSize) + ' ' + @returnFields + ' FROM ' + @tableName + ' WHERE ' + @whereFields + ' ' + @ORD
   END ELSE BEGIN
    SET @SQL = 'SELECT TOP ' + STR(@pageSize) + ' ' + @returnFields + ' FROM ' + @tableName + ' '+ @ORD
   END
  END ELSE BEGIN   
   IF (@whereFields <> '')
   BEGIN
    SET @SQL = 'SELECT TOP ' + STR(@pageSize) + ' ' + @returnFields + ' FROM ' + @tableName + ' WHERE ' + @orderFields + ' ' + @TMP + ' ( ' + @orderFields + ' ) FROM ( SELECT TOP ' + STR( ( @pageIndex - 1 ) * @pageSize ) + ' ' + @orderFields + ' FROM ' + @tableName + ' WHERE ' + @whereFields + ' ' + @ORD + ' ) AS tbTMP ) AND ' + @whereFields + ' ' + @ORD    
   END ELSE BEGIN
    SET @SQL = 'SELECT TOP ' + STR(@pageSize) + ' ' + @returnFields + ' FROM ' + @tableName + ' WHERE ' + @orderFields + ' ' + @TMP + ' ( ' + @orderFields + ' ) FROM ( SELECT TOP ' + STR( ( @pageIndex - 1 ) * @pageSize ) + ' ' + @orderFields + ' FROM ' + @tableName + ' ' + @ORD + ' ) AS tbTMP ) '+ @ORD
   END  
  END  
 END
 print @SQL
 EXEC (@SQL)

 

--------------------------------------------------------------------------------------------------------------------------

DECLARE @return_value int

EXEC @return_value = [dbo].[procedure_search_record]
  @tableName = N'tbFileNameRecord',
  @returnFields = N'Id,RegisterId,FileNameId,CreateTime',
  @orderFields = N'Id',
  @pageSize = 25,
  @pageIndex = 5,
  @returnCount = 0,
  @orderType = 0,
  @whereFields = N'RegisterId in (123,456,789)'
GO