mysql - 按特殊顺序对表记录进行排序

Question

我有 table :

+----+--------+----------+
| id | doc_id | next_req | 
+----+--------+----------+
|  1 |    1   |     4    | 
|  2 |    1   |     3    | 
|  3 |    1   |     0    | 
|  4 |    1   |     2    | 
+----+--------+----------+

id - 自动递增主键。

nex_req - 代表记录顺序。 (next_req = 记录的id)

我如何构建一个 SQL 查询以获取按此顺序的记录:

+----+--------+----------+
| id | doc_id | next_req | 
+----+--------+----------+
|  1 |    1   |     4    | 
|  4 |    1   |     2    | 
|  2 |    1   |     3    | 
|  3 |    1   |     0    | 
+----+--------+----------+

解释:

record1 with id=1 and next_req=4 means: next must be record4 with id=4 and next_req=2
record4 with id=5 and next_req=2 means: next must be record2 with id=2 and next_req=3 
record2 with id=2 and next_req=3 means: next must be record3 with id=1 and next_req=0 
record3 with id=3 and next_req=0: means that this is a last record 

我需要在表中存储记录的顺序。这对我很重要。

Answer 1

如果可以，请更改您的表格格式。不要命名下一条记录，而是按顺序标记记录，以便您可以使用自然的 SQL 排序:

+----+--------+------+
| id | doc_id | sort | 
+----+--------+------+
|  1 |    1   |  1   | 
|  4 |    1   |  2   | 
|  2 |    1   |  3   | 
|  3 |    1   |  4   | 
+----+--------+------+

然后你甚至可以在 doc_id 上建立集群索引，如果你需要为性能问题排序。老实说，如果您需要对行进行重新排序，这并不比您之前使用的链表更麻烦。