如何在mysql中获得最近的行？

Question

假设我正在构建一个图片库的缩略图(5个缩略图显示)。如何让我的mysql显示5个缩略图，中间的缩略图将被放大，中间左边的两个将是先前的图像，而中间右边的另外两个图像将在后面再显示2个。

编辑

我知道如何用这样的方法很容易地获得5个缩略图

$sqlThumb = mysql_query("SELECT iID,thumbnails,userID FROM images WHERE userID = ".$_SESSION['userID']." ORDER BY iID ASC LIMIT 1,5");

但问题是，我没有一个一致的id (意思是，将删除和插入)。它基于用户拥有的图像。

iID            userID 
-----          --------
1              5
2              4
3              4
4              5
5              5
6              5
10             5
11             5
12             5

因此，如果用户点击一个iID号为5的图片，缩略图应该按顺序显示如下，1,4, 5，6,10。

但是，如果用户单击缩略图数字10，缩略图应该显示:5、6、10、11、12。

Answer 1

尝尝这个

$offset = $current_id-3;

 (SELECT * FROM TBL WHERE id < '$current_id'    limit '$offset',2 )
 union( SELECT * FROM TBL WHERE id = '$current_id') 
 union (SELECT * FROM TBL WHERE id > '$current_id' limit 2)

Answer 2

我不知道这是否有效，但想法是有一个名为row_number的属性。您将通过id和两个最后一个以及下一个行号获得您想要的图像。

SELECT iID,thumbnail FROM image_collection,
   (SELECT iID, thumbnail, row_number
   FROM
      (SELECT iID,thumbnail,
      @curRow := @curRow + 1 AS row_number
      FROM image
      WHERE userID = 5
      ORDER BY iID) as image_collection
   WHERE s_image.iID = 10) as image_selected
WHERE image_collection.row_number BETWEEN image_selected.row_number - 2 AND image_selected.row_number + 2

我没有测试它，因为我没有运行MySQL，但是我知道如何将行号放在这里：With MySQL, how can I generate a column containing the record index in a table?

Answer 3

试试这个(不包括任何减法)：

$id = 5;
$user_id = $_SESSION['userID'];

$sqlThumb = mysql_query("
SELECT iID FROM images WHERE userID = ".$user_id." AND iID = ".$id."
  UNION (SELECT iID FROM images WHERE userID = ".$user_id." AND iID < ".$id." ORDER BY iID DESC LIMIT 2) 
  UNION (SELECT iID FROM images WHERE userID = ".$user_id." AND iID > ".$id." ORDER BY iID ASC LIMIT 2) 
  ORDER BY iID ASC")