代码之家 › 专栏 › 技术社区 › Silver Light

MySQL查询:当列表使用非唯一值排序时获取下一个或上一个项

mysql php

Silver Light · 技术社区 · 14 年前

我有图片库。每个图片都有一个基于多少用户投票“我喜欢这个”的评级。mysql表lokes如下:

id       |image      |rating
------------------------------------
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
166      |37.png     |4
342      |95.png     |2
99       |35.png     |0
76       |34.png     |0
44       |3.png      |0
8        |22.png     |0

任务是:使查看按分级排序的库成为可能。很容易列出图像使用 ORDER BY rating DESC, id DESC 但当用户点击图片时,问题就出现了,我必须在打开的图片旁边显示“上一张图片”和“下一张图片”按钮。

假设我们现在从示例表中查看id=169的图像:

id       |image      |rating
------------------------------------
169      |99.png     |8

如何编写查询以获取上一个图像(ID=171)?问题是,主要的订货方式是 rating (不是唯一的)第二个是唯一的 id . 在任何情况下,哪个查询都会给出正确的前一个图像。

我试过了:

SELECT *
FROM images
WHERE rating >= 8 AND id >=169
ORDER BY rating, id
LIMIT 1

但没有,因为 id >=169 只有当评分相同时才必须进行检查。

我有点困惑,请帮帮我。

更新

我自己想的。假设我有 评级 和 身份证件 当前记录的值,获取上一条记录的查询将是:

SELECT *
FROM `images`
WHERE 
    (`rating` = 8 AND `id` > 169)
    OR `rating` > 8
ORDER BY `rating`, `id`
LIMIT 1

如果 rating = 8 AND id > 169 不进行评估,则 rating > 8 开始游戏。结果很简单。

谢谢大家!接受大多数赞成票的回答。

5 回复 | 直到 14 年前

sfussenegger 14 年前

SELECT * FROM images ORDER BY rating DESC, id LIMIT $n, 1

在哪里? $n 是从0开始的计数器。按ID排序确保后续调用始终保持相同的顺序。因此,下一个图像将是 $n+1 ,以前 $n-1 .

此外,如果要确保当前图像有下一个图像(用户当然不喜欢单击“下一个”以获得404错误),则可以始终获取2个图像。

编辑:来自注释的新需求

嗯,你可以试着计算一个与你当前图像的最小距离。

SELECT * from images
WHERE id < $current[id] AND rating >= $current[rating] 
ORDER by ((abs($current[rating] - rating) << 32) + abs($current[id] - id))
LIMIT 1

SELECT * from images
WHERE id > $current[id] AND rating <= $current[rating]
ORDER by ((abs($current[rating] - rating) << 32) + abs($current[id] - id))
LIMIT 1

不过,不要问我任何关于性能的问题;)

0xCAFEBABE 14 年前

您正在寻找一种面向光标的方法。另一个非常简单的方法是通过特定的排序条件仅选择所有ID,将它们保存在应用程序中,然后使用特定选择所需行的ID和查询进行浏览和选择。

cww 14 年前

试试这个,换个 test 到您的表名并替换 curr.id =169 到当前页ID

   SELECT curr.id AS currid, curr.image as curr_img, 
           prev.id AS previd, prev.image as prev_img,
           next.id AS nextid, next.image as next_img
    FROM test curr
    LEFT JOIN test prev
       ON prev.id != curr.id
       AND (
          prev.rating > curr.rating
          OR (
                 prev.rating = curr.rating
                 AND prev.id > curr.id
          )
       )
    LEFT JOIN test next
        ON next.id != curr.id
        AND (
           next.rating < curr.rating
           OR (
                  next.rating = curr.rating
                  AND next.id < curr.id
           )
        )
    WHERE curr.id =169
    ORDER BY prev.rating ASC , next.rating DESC , prev.id ASC , next.id DESC
    LIMIT 1

fredley 14 年前

在一个查询中获取所有条目,将其放入一个PHP数组中,然后改为使用它!

Jon Black 14 年前

我记得我在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);