分页总结

亦凉 2022-07-16 19:44 206阅读 0赞

\---1、通用型  
Create PROC TrkJob\_GetPaged  
(  
 @WhereClause varchar (2000)  ,---所有查询条件的组合  
 @OrderBy varchar (2000)  , ---排序  
 @PageIndex int   ,   ---页索引  
 @PageSize int    ---页数据行数  
)  
AS

BEGIN  
 DECLARE @PageLowerBound int  
 DECLARE @PageUpperBound int

-- Set the page bounds  
 SET @PageLowerBound = @PageSize \* @PageIndex  
 SET @PageUpperBound = @PageLowerBound + @PageSize

IF (@OrderBy IS NULL OR LEN(@OrderBy) < 1)  
 BEGIN  
  -- default order by to first column  
  SET @OrderBy = '\[CompanyCode\]'  
 END  
 DECLARE @SQL AS nvarchar(MAX)  
 SET @SQL = 'WITH PageIndex AS ('  
 SET @SQL = @SQL + ' SELECT'  
 IF @PageSize > 0  
 BEGIN  
  SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)  
 END  
 SET @SQL = @SQL + ' ROW\_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'  
 SET @SQL = @SQL + ', \[CompanyCode\]'  
 SET @SQL = @SQL + ', \[JobNo\]'  
 SET @SQL = @SQL + ', \[RowVersion\]'  
 SET @SQL = @SQL + ' FROM \[dbo\].\[TrkJob\]'  
 IF LEN(@WhereClause) > 0  
 BEGIN  
  SET @SQL = @SQL + ' WHERE ' + @WhereClause  
 END  
 SET @SQL = @SQL + ' ) SELECT'  
 SET @SQL = @SQL + ' \[CompanyCode\],'  
 SET @SQL = @SQL + ' \[JobNo\],'  
 SET @SQL = @SQL + ' \[RowVersion\]'  
 SET @SQL = @SQL + ' FROM PageIndex'  
 SET @SQL = @SQL + ' WHERE RowIndex > ' + CONVERT(nvarchar, @PageLowerBound)  
 IF @PageSize > 0  
 BEGIN  
  SET @SQL = @SQL + ' AND RowIndex <= ' + CONVERT(nvarchar, @PageUpperBound)  
 END  
 SET @SQL = @SQL + ' ORDER BY ' + @OrderBy  
 EXEC sp\_executesql @SQL  
   
 -- get row count  
 SET @SQL = 'SELECT COUNT(\*) AS TotalRowCount'  
 SET @SQL = @SQL + ' FROM \[dbo\].\[TrkJob\]'  
 IF LEN(@WhereClause) > 0  
 BEGIN  
  SET @SQL = @SQL + ' WHERE ' + @WhereClause  
 END  
 EXEC sp\_executesql @SQL

END  
    
\---方案二和三 要求 JobNo必须是整型  
\--2.方案二：(利用Not In和SELECT TOP分页)

SELECT TOP 30 \*  
FROM TrkJob  
WHERE (JobNo NOT IN  
  (SELECT TOP (30\*(2-1)) JobNo  
  FROM TrkJob  
  ORDER BY JobNo))  
  ORDER BY JobNo

\--3.分页方案二：(利用JobNo大于多少和SELECT TOP分页）  
SELECT TOP 30 \*  
FROM TrkJob  
WHERE (JobNo >  
          (SELECT MAX(JobNo)  
    FROM (SELECT TOP 30\*2 JobNo  FROM TrkJob  
          ORDER BY JobNo) AS T)  
      )  
ORDER BY JobNo