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)