|
--一个简单的分页语句，受限与主键必须是连续自增的
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)
--调用
exec GetPageList @pagesize=3
--上面执行产生的SQL语句 SELECT TOP 3 * FROM (SELECT * FROM course) f1 ORDER BY [co_id] DESC
exec GetPageList @pagesize=3,@pageindex=2
--上面执行产生的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%'"
--上面执行产生的SQL语句 SELECT TOP 3 * FROM (SELECT * FROM course where co_name like '%a%') f1 ORDER BY [co_id] DESC
|
|
|
