mysql先排序再取唯一值_MySQL查询:当列表使用非唯一值排序时获取下一个或上一个项...

我记得我在MySQL性能博客上读到一篇关于

pagination

把这个例子搞得一团糟,可能会有帮助(@row_id是多余的)

drop table if exists gallery;

create table gallery

(

id int unsigned not null,

image varchar(255) not null,

rating tinyint unsigned default 0

)

engine=innodb;

insert into gallery values

(166,'6.png',9),(165,'8.png',9),(189,'1.png',8),

(171,'99.png',8),(169,'56.png',8),(155,'34.png',8),

(265,'7.png',7),(754,'86.png',6),(37,'37.png',4),

(342,'95.png',2),(99 ,'35.png',0),(76 ,'34.png',0),

(44 ,'3.png',0),(8 ,'22.png',0), (1001 ,'1001.png',0);

drop procedure if exists list_gallery_paged;

delimiter #

create procedure list_gallery_paged

(

in p_last_id int unsigned,

in p_last_rating tinyint unsigned

)

proc_main:begin

set @row_id = 0;

if p_last_id <= 0 then

select @row_id:=@row_id+1 as row_id, g.*

from gallery g order by rating desc, id desc limit 4;

else

select @row_id:=@row_id+1 as row_id, g2.*

from gallery g inner join gallery g2 on g.id = g2.id

where

g.rating <= p_last_rating and (g.id < p_last_id or g.rating < p_last_rating)

order by

g.rating desc, g.id desc limit 4;

end if;

end proc_main #

delimiter ;

-- in pages of 4 (use all rows)

select g.* from gallery g order by rating desc, id desc;

call list_gallery_paged(0,0);

call list_gallery_paged(171,8);

call list_gallery_paged(754,6);

call list_gallery_paged(99,0);

call list_gallery_paged(8,0);

-- one at a time (use top row only)

select g.* from gallery g order by rating desc, id desc;

call list_gallery_paged(265,7);

call list_gallery_paged(754,6);

call list_gallery_paged(37,4);

call list_gallery_paged(342,2);

call list_gallery_paged(1001,0);

call list_gallery_paged(99,0);

call list_gallery_paged(76,0);

call list_gallery_paged(44,0);

call list_gallery_paged(8,0);