PHP/MYSQL 查询：获取结果上方和下方的行！

Question

我不知何故需要一种方法来实现以下目的：

用户位于某个博客页面现在我想让他能够移动：

到下一个更受欢迎的页面或到下一个不太受欢迎的页面。意思是，像这样：（

[PAGE 5] | [PAGE 3 (User is here)] | [PAGE 2]

其中第 1 页 = 最受欢迎，第 3 页 = 最不受欢迎）。我的 MySQL 表如下所示：

[ID] [VIEWS]
[1] [1000]
[2] [2560]
[3] [3200]
[4] [200]
[5] [4000]

我的问题是特定的查询。在本例中，唯一给定的变量是 ID：2。也许你可以帮我。如果您需要更多信息，请告诉我。 （我只需要两个邻居。不需要第 4 页等。）

编辑：@Trevor 不，抱歉我不能。我改变了这个例子，使它更清楚。

Answer 1

注意：根据各种评论，@mellamokb 的答案比我的好。我会删除这个答案，但由于它被接受而无法删除。

/* Next Page */
SELECT ID
    FROM YourTable
    WHERE VIEWS <= (SELECT VIEWS FROM YourTable WHERE ID = $CurrentPageId)
        AND ID < $CurrentPageId
    ORDER BY VIEWS DESC, ID DESC LIMIT 1

/* Previous Page */
SELECT ID
    FROM YourTable
    WHERE VIEWS >= (SELECT VIEWS FROM YourTable WHERE ID = $CurrentPageId)
        AND ID > $CurrentPageId
    ORDER BY VIEWS, ID LIMIT 1

Answer 2

上一页：

  select ID as PreviousId
    from PageViews
   where Views > (select Views From PageViews Where ID = @Id)
      or (
             Views = (select Views From PageViews Where ID = @Id)
             and ID > @id
         )
order by Views ASC, ID DESC
   limit 1

下一页：

  select ID as PreviousId
    from PageViews
   where Views < (select Views From PageViews Where ID = @Id)
      or (
             Views = (select Views From PageViews Where ID = @Id)
             and ID < @id
         )
order by Views DESC, ID DESC
   limit 1

Answer 3

或者

    /* Previous Page */
SELECT ID
    FROM YourTable
    WHERE VIEWS < $CurrentPageViews 
    ORDER BY VIEWS DESC LIMIT 1

/* Next Page */
SELECT ID
    FROM YourTable
    WHERE VIEWS > $CurrentPageViews
    ORDER BY VIEWS LIMIT 1

Answer 4

三个查询：

SELECT @MyID:=ID,@MyViews:=Views FROM Table WHERE ID=2;

使用本地“视图”：

SELECT ID,Views FROM Table WHERE Views <= @MyViews AND ID != @MyID ORDER BY Views DESC LIMIT 1;
SELECT ID,Views FROM Table WHERE Views >= @MyViews AND ID != @MyID ORDER BY Views ASC LIMIT 1;

并且，确保您在视图上有索引。