|
--一個簡單的分頁語句，受限與主鍵必須是連續(xù)自增的
select top 3 * from course
where co_id>all(select top 3 co_id from course order by co_id)
order by co_id desc
--course = 表名 ， co_id=主鍵字段
alter PROCEDURE GetPageList
@pagesize int,
@pageindex int=1,
@where varchar(300)=''
AS
DECLARE @strSQL varchar(5000)
if @where=''
begin
SET @strSQL='SELECT * FROM course'
end
else
begin
SET @strSQL='SELECT * FROM course where'+@where
end
IF @pageindex = 1
BEGIN
SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' * FROM (' + @strSQL + ') f1' + ' ORDER BY [co_id] DESC'
END
ELSE
BEGIN
SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' * FROM (' + @strSQL + ') f1 WHERE [co_id] < (SELECT MIN([co_id]) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [co_id] FROM (' + @strSQL + ') f2' + ' ORDER BY [co_id] DESC) AS tblTmp)' + ' ORDER BY [co_id] DESC'
END
EXEC(@strSQL)
print(@strSQL)
--調(diào)用
exec GetPageList @pagesize=3
--上面執(zhí)行產(chǎn)生的SQL語句 SELECT TOP 3 * FROM (SELECT * FROM course) f1 ORDER BY [co_id] DESC
exec GetPageList @pagesize=3,@pageindex=2
--上面執(zhí)行產(chǎn)生的SQL語句 SELECT TOP 3 * FROM (SELECT * FROM course) f1 WHERE [co_id] < (SELECT MIN([co_id]) FROM (SELECT TOP 3 [co_id] FROM (SELECT * FROM course) f2 ORDER BY [co_id] DESC) AS tblTmp) ORDER BY [co_id] DESC
exec GetPageList @pagesize=3,@where=" co_name like '%a%'"
--上面執(zhí)行產(chǎn)生的SQL語句 SELECT TOP 3 * FROM (SELECT * FROM course where co_name like '%a%') f1 ORDER BY [co_id] DESC
|
|
|
