drop temporary table if exists tmp_info; create temporary table if not exists tmp_info(id int auto_increment,layer tinyint,description varchar(100),primary key(id))engine=heap;set @layer1=0; set @layer2=0; set @layer3=0;select * from ( select a.id,case when a.layer=1 then @layer1:=@layer1+1 when a.layer=2 then @layer2:=@layer2+1 when a.layer=3 then @layer3:=@layer3+1 end as layernum,a.layer,a.description from tmp_info a order by a.description )aa where case when aa.layer=1 then ceil(aa.layernum/5)=@pagenum when aa.layer=2 then ceil(aa.layernum/3)=@pagenum when aa.layer=3 then ceil(aa.layernum/2)=@pagenum end;drop temporary table if exists tmp_info;由于不知道你的表结构,我就初始化了tmp_info作为测试表,这样可以读出来10条数据
create temporary table if not exists tmp_info(id int auto_increment,layer tinyint,description varchar(100),primary key(id))engine=heap;set @layer1=0;
set @layer2=0;
set @layer3=0;select *
from (
select a.id,case when a.layer=1 then @layer1:=@layer1+1
when a.layer=2 then @layer2:=@layer2+1
when a.layer=3 then @layer3:=@layer3+1 end as layernum,a.layer,a.description
from tmp_info a
order by a.description
)aa
where case when aa.layer=1 then ceil(aa.layernum/5)=@pagenum when aa.layer=2 then ceil(aa.layernum/3)=@pagenum
when aa.layer=3 then ceil(aa.layernum/2)=@pagenum end;drop temporary table if exists tmp_info;由于不知道你的表结构,我就初始化了tmp_info作为测试表,这样可以读出来10条数据