mysql - 根据排名和先前选择的行选择行

Question

我正在尝试做一个论坛排名系统，它可以根据以前的记录一次获得一条记录。

示例表是:

| POST_ID | POST_REPLIES |--------------------------|   1     |        5     | |   2     |        2     | |   3     |        8     ||   4     |        8     ||   5     |        12    |--------------------------

If I do a simple query ORDER BY POST_REPLIES DESC, I get POST_ID 5, 4, 3, 1, 2.

But what I want to do is get onlythe next row (so a single row at a time), and based on the post it is currently at.

For example: if I am currently viewing post #3, there would be a button labeled 'next post with most replies' which would point to post # 4.

I am currently having trouble dealing with duplicates, as I run into a loop between 3 and 4 (3 points to 4, and 4 points to 3 rather than 5)

I had played around with joining the table onto itself and comparing the rows to see which one was greater or less, but since I am using a limit of 1, the row is always 1 and thus useless. So the basic query I had was:

SELECT * FROM posts
   WHERE post_id != '$currentPost' 
   ORDER BY POST_REPLIES DESC, POST_ID DESC LIMIT 1

我该怎么做？

Answer 1

您需要做的第一步是对结果进行“排名”。我发现在 MySQL 中执行此操作的最佳方法是使用如下变量:

SELECT posts.post_id, posts.post_replies, @rank := @rank + 1 AS rank
FROM posts, (SELECT @rank := 0) r

然后您可能必须将该查询嵌套在另一个查询中才能完成您的需要。让我知道这是否为您指明了正确的方向