本人在MSSQL上写了一个用语分页SQL语句,就是单纯在数据库层实现分页的SQL代码
,代码如下:use ftp
declare @pageSize int --返回一页的记录数
declare @CurPage int --页号(第几页)0:第一页;-1最后一页。
declare @MaxId int
declare @MaxPage int
declare @Count int
declare @id int
set @pageSize=6
set @CurPage =1
select @MaxId=MAX(id) from ftp
select @MaxPage =ceiling(1.0*@MaxId/@pageSize)
--定位
if @CurPage<=0
begin
set rowcount @MaxId
select @id=id from ftp order by id
endif @CurPage > 0 and @CurPage <= @MaxPage
begin
set @Count = @pageSize * (@CurPage -1) + 1
set rowcount @Count
select @id=id from ftp order by id desc
end
if @CurPage>@MaxPage
begin
set rowcount @MaxId
select @id=id from ftp order by id
end
--返回记录
set rowcount @pageSize
select * from ftp where id<=@id order by id desc
set rowcount 0经过测试,MSSQL可以运行并可返回正确的查询结果.
但同样的代码在MYSQL上无法运行,报错信息如下,希望高手指点
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare int pageSize
declare int CurPage
declare int MaxId
declare int Ma' at line 1
,代码如下:use ftp
declare @pageSize int --返回一页的记录数
declare @CurPage int --页号(第几页)0:第一页;-1最后一页。
declare @MaxId int
declare @MaxPage int
declare @Count int
declare @id int
set @pageSize=6
set @CurPage =1
select @MaxId=MAX(id) from ftp
select @MaxPage =ceiling(1.0*@MaxId/@pageSize)
--定位
if @CurPage<=0
begin
set rowcount @MaxId
select @id=id from ftp order by id
endif @CurPage > 0 and @CurPage <= @MaxPage
begin
set @Count = @pageSize * (@CurPage -1) + 1
set rowcount @Count
select @id=id from ftp order by id desc
end
if @CurPage>@MaxPage
begin
set rowcount @MaxId
select @id=id from ftp order by id
end
--返回记录
set rowcount @pageSize
select * from ftp where id<=@id order by id desc
set rowcount 0经过测试,MSSQL可以运行并可返回正确的查询结果.
但同样的代码在MYSQL上无法运行,报错信息如下,希望高手指点
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare int pageSize
declare int CurPage
declare int MaxId
declare int Ma' at line 1
delimiter //
CREATE PROCEDURE`pagination`(IN pagesize INT,IN currpage INT)
DETERMINISTIC
begin
declare Idmax int;
declare lowerbound int;
declare maxpage int;
select max(id) from ftp into Idmax;
select ceiling(Idmax/pagesize) into maxpage;
set lowerbound=pagesize*(currpage-1)+1;
if ((currpage> 0)or(currpage<=maxpage))
then
select * from ftp order by id desc limit lowerbound,pagesize;
else
select * from ftp order by id desc limit 0,pagesize;
end if;
end //
但是还是有错!!