MySQL下一个/上一个ID(任意排序顺序) [英] MySQL next/previous ids (arbitrary sort order)

问题描述

这是我的桌子:

mysql> describe ps;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment | 
| a     | text    | YES  |     | NULL    |                | 
| b     | text    | YES  |     | NULL    |                | 
| c     | text    | YES  |     | NULL    |                | 
+-------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

假设我有以下数据:

mysql> select * from ps;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 | x    | x    | x    | 
|  2 | x    | y    | x    | 
|  3 | x    | z    | x    | 
|  4 | x    | x    | y    | 
|  5 | x    | x    | z    | 
|  6 | x    | y    | x    | 
|  7 | x    | y    | y    | 
|  8 | x    | y    | z    | 
|  9 | y    | x    | x    | 
| 10 | z    | x    | x    | 
| 11 | y    | y    | x    | 
| 12 | y    | z    | x    | 
| 13 | y    | x    | y    | 
| 14 | y    | x    | z    | 
| 15 | z    | x    | x    | 
| 16 | z    | y    | x    | 
| 17 | z    | z    | x    | 
| 18 | z    | x    | y    | 
| 19 | z    | x    | z    | 
+----+------+------+------+
19 rows in set (0.00 sec)

我的查询是:

mysql> select * from ps where b = 'x' order by c;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 | x    | x    | x    | 
|  9 | y    | x    | x    | 
| 10 | z    | x    | x    | 
| 15 | z    | x    | x    | 
|  4 | x    | x    | y    | 
| 13 | y    | x    | y    | 
| 18 | z    | x    | y    | 
|  5 | x    | x    | z    | 
| 14 | y    | x    | z    | 
| 19 | z    | x    | z    | 
+----+------+------+------+
10 rows in set (0.00 sec)

假设我的ID为4.因此，我知道我的ID为4，条件为b ='x'，顺序为c.我想获得下一行(ID为13)和上一行(ID为15).我可以通过SQL查询从数据库中找到这些，还是必须检索所有内容并在我的代码中循环遍历?

Let's say I have the row with id 4. So, I know that I'm at id 4 and that the condition is b = 'x' and the order is c. I want to get the next row (which is id 13) and the previous one (which is id 15). Can I find these from the database through SQL queries, or do I have to retrieve everything and loop through it in my code?

推荐答案

您可以使用limit子句来做到这一点.

You can use the limit clause to do this.

但是，当数据已经在PHP中时，在sql中执行此操作似乎没有任何意义.为什么不将行放入每行一个元素的数组中，并使用数组的指针来实现这一点呢?

But there doesn't seem to be any point in doing this in sql when the data is already in PHP. Why not put the rows into an array with an element per row, and use the array's pointer to achieve this?

您需要的只是prev()，current()和next()函数.

The prev(), current() and next() functions should be all you need.

这篇关于MySQL下一个/上一个ID(任意排序顺序)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！