MySql:按父级和子级排序

I have a table like:

我有一张桌子:

+------+---------+-
| id   | parent  |
+------+---------+
| 2043 |    NULL |
| 2044 |    2043 |
| 2045 |    2043 |
| 2049 |    2043 |
| 2047 |    NULL |
| 2048 |    2047 |
| 2043 |    2047 |
+------+---------+

which shows a simple, 2-level "parent-child"-corelation. How can I ORDER BY an SELECT-statement to get the order like in the list above, which means: 1st parent, childs of 1st parent, 2nd parent, childs of 2nd parent and so on (if I have that, I can add the ORDER BYs for the children... I hope). Is it possible withoug adding a sort-field?

这显示了一个简单的，2级的“亲子”-corelation。如何通过一个select语句进行排序以获得上面列表中的顺序，这意味着:1父、1父、2父、2父、2父的孩子等等(如果我有的话，我可以为孩子添加BYs订单……)我希望)。有没有可能加入一个排序域?

3 个解决方案

#1

Including sorting children by id:

包括按身份证分拣儿童:

ORDER BY COALESCE(parent, id), parent IS NOT NULL, id

SQL Fiddle example

SQL小提琴的例子

Explanation:

解释:

COALESCE(parent, id): First sort by (effectively grouping together) the parent's id.
联合(父id):首先通过(有效地分组在一起)父id进行排序。
parent IS NOT NULL: Put the parent row on top of the group
父类不是NULL:将父行放在组的顶部。
id: Finally sort all the children (same parent, and parent is not null)
id:最后对所有子节点进行排序(相同的父节点和父节点不是null)

#2

If your table uses 0 instead of null to indicate an entry with no parent:

如果您的表使用0而不是null来表示没有父元素的条目:

id   | parent
-------------
1233 | 0
1234 | 1233
1235 | 0
1236 | 1233
1237 | 1235

Use greatest instead of coalesce and check the value does not equal 0:

用最大代替合并，检查数值不等于0:

ORDER BY GREATEST(parent, id), parent != 0, id

#3

The solution above didn't work for me, my table used 0 instead of NULL. I found this other solution: you create a column with the concatened parent id and child id in your query and you can sort the result by it .

上面的解对我不起作用，我的表用0而不是NULL。我发现了另一种解决方案:您在查询中创建一个包含已连接的父id和子id的列，您可以通过它对结果进行排序。

SELECT CONCAT(IF(parent = 0,'',CONCAT('/',parent)),'/',id) AS gen_order
FROM table 
ORDER BY gen_order

#1