从自引用mysql表中检索父子层次结构 [英] Retrieve parent-child hierarchy from a self-referencing mysql table

问题描述

我有一个这样的mysql表结构(删除了其他列，与该问题无关):

I have a mysql table structured like this (removed other columns, not relevant to the question):

id          parent_id
----------- -----------
1           0          
2           0            
3           0          
4           3          
5           2
6           1
7           1          

父"是具有parent_id = 0的行.

我想知道是否有一种公式化查询，以便MySQL在紧随其父之后输出带有每个子级的行?

I would like to know if there is a way to formulate the query so that MySQL outputs the rows with each children right after its parents?

预期结果将是

id          parent_id
----------- -----------
1           0          
6           1            
7           1          
2           0          
5           2          
3           0          
4           3          

谢谢您的时间.

推荐答案

MySQL不支持递归查询，因此，如果孩子可以有孩子，则无法制定这样的查询.否则，此查询应按所需顺序返回行:

MySQL does not support recursive queries, so if a child can have childs there's no way to formulate such a query. Otherwise this query should return the rows in the order that you need:

SELECT * FROM tablename
ORDER BY
  CASE WHEN parent_id=0 THEN id ELSE parent_id END,
  id

请在此处查看小提琴.诀窍是如果id是父级，则按id排序；如果是孩子，则按parent_id排序，然后按id排序行.

Please see fiddle here. The trick is to order rows by id if it's a parent or by parent_id if it's a child, and then by id.

这篇关于从自引用mysql表中检索父子层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！