SQL TOP分页法

原理：

PageSize， PageIndex；

 

升序   order  by  a asc

a的 值 大于， 上一页数据 最大的  a值。

 

降序  order by  a desc

a的 值 小于， 上一页数据 最小的  a值。

 

代码实例

 

USE [SZSQ]
GO
/****** Object: StoredProcedure [dbo].[dnt_gettopiclistbytypedatecph] Script Date: 09/12/2014 15:18:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[dnt_PageSplb]
@pagesize int,
@pageindex int,
@startnum int,
@condition varchar(1000),
@orderby varchar(100),
@ascdesc int
AS

DECLARE @strsql varchar(5000)
DECLARE @sorttype varchar(5)

IF @ascdesc=0
SET @sorttype='ASC'
ELSE
SET @sorttype='DESC'

IF @pageindex = 1
BEGIN
SET @strsql = 'SELECT TOP ' + STR(@pagesize) +' [sid],[sname],[sauthor],[sdate],[simg],[slink],[spjs],[slbdl],[slbps],[slbgjw],[slbql],[slbjs],[sbfcs],[splcs],[szdcs],[scdcs],[ssfsx]
from [dnt_splb] where [ssfsx]=1'+@condition+' ORDER BY '+@orderby+' '+@sorttype
END
ELSE
IF @sorttype='DESC'
BEGIN
SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [sid],[sname],[sauthor],[sdate],[simg],[slink],[spjs],[slbdl],[slbps],[slbgjw],[slbql],[slbjs],[sbfcs],[splcs],[szdcs],[scdcs],[ssfsx]
FROM [dnt_splb]
WHERE dnt_splb.['+@orderby+'] < (SELECT min(tblTmp.['+@orderby+']) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize-@startnum) + ' [dnt_splb].['+@orderby+'] FROM [dnt_splb] WHERE [ssfsx]=1'+@condition+' ORDER BY [dnt_splb].'+@orderby+' '+@sorttype+')AS tblTmp )
AND [ssfsx]=1
ORDER BY [dnt_splb].'+@orderby+' '+@sorttype
END
ELSE
BEGIN
SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [sid],[sname],[sauthor],[sdate],[simg],[slink],[spjs],[slbdl],[slbps],[slbgjw],[slbql],[slbjs],[sbfcs],[splcs],[szdcs],[scdcs],[ssfsx]
FROM [dnt_splb]
WHERE dnt_splb.['+@orderby+'] > (SELECT min(tblTmp.['+@orderby+']) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize-@startnum) + ' [dnt_splb].['+@orderby+'] FROM [dnt_splb] WHERE [ssfsx]=1'+@condition+' ORDER BY [dnt_splb].'+@orderby+' '+@sorttype+')AS tblTmp )
AND [ssfsx]=1
ORDER BY [dnt_splb].'+@orderby+' '+@sorttype
END
print @strSQL
EXEC(@strsql)

 

转载于:https://www.cnblogs.com/sunzhenyong/p/3974245.html