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)

巴特西

SQL TOP分页法

最新文章

热门文章