南艺宿舍:分页存储过程怎么写?
来源:百度文库 编辑:中科新闻网 时间:2024/07/03 08:50:10
Product表中字段有productID(自动编号),Name(名称),hits(点击数).
有这样的分页存储过程:
CREATE PROCEDURE [GetProductDataPage1]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
SELECT @RecordCount =COUNT(*) FROM Product
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+
'* FROM Product ORDER BY ProductID DESC'
ELSE IF @PageIndex = @PageCount - 1
SET @SQLSTR =N' SELECT * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+
'* FROM Product ORDER BY ProductID ASC) TempTable ORDER BY ProductID DESC'
ELSE
SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+' * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+
'* FROM Product ORDER BY ProductID ASC ) TempTable ORDER BY ProductID DESC '
EXEC (@SQLSTR)
GO
上面按照ProductID排序
如果按照hits(点击数)排序应该怎么改写??
(不能把上面存储过程的ProductID全部替换为hits,因为测试时设置页数为1,那么显示的每页的结果将发生重复.)
请问谁能写出正确的按hits分页的存储工程呢??
注:hits字段是可以重复的.
有这样的分页存储过程:
CREATE PROCEDURE [GetProductDataPage1]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
SELECT @RecordCount =COUNT(*) FROM Product
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+
'* FROM Product ORDER BY ProductID DESC'
ELSE IF @PageIndex = @PageCount - 1
SET @SQLSTR =N' SELECT * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+
'* FROM Product ORDER BY ProductID ASC) TempTable ORDER BY ProductID DESC'
ELSE
SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+' * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+
'* FROM Product ORDER BY ProductID ASC ) TempTable ORDER BY ProductID DESC '
EXEC (@SQLSTR)
GO
上面按照ProductID排序
如果按照hits(点击数)排序应该怎么改写??
(不能把上面存储过程的ProductID全部替换为hits,因为测试时设置页数为1,那么显示的每页的结果将发生重复.)
请问谁能写出正确的按hits分页的存储工程呢??
注:hits字段是可以重复的.
看的头晕