MySql:父级和子级ORDER BY [英] MySql: ORDER BY parent and child
本文介绍了MySql:父级和子级ORDER BY的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张桌子,像这样:
I have a table like:
+------+---------+-
| id | parent |
+------+---------+
| 2043 | NULL |
| 2044 | 2043 |
| 2045 | 2043 |
| 2049 | 2043 |
| 2047 | NULL |
| 2048 | 2047 |
| 2043 | 2047 |
+------+---------+
展示了一个简单的两级父子" -corelation.我如何通过SELECT语句进行排序来获得如上列表中所示的顺序,这意味着:第一亲,第二亲,第二亲,第二亲等等(如果我有的话,我可以添加为孩子们订购订单...我希望).是否可以添加排序字段?
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?
推荐答案
包括按ID对子级进行排序:
Including sorting children by id:
ORDER BY COALESCE(parent, id), parent IS NOT NULL, id
说明:
-
COALESCE(parent, id)
:首先按(有效分组)父母的ID排序. -
parent IS NOT NULL
:将父行放在组的顶部 -
id
:最后对所有子级进行排序(父级相同,并且parent
不为空)
COALESCE(parent, id)
: First sort by (effectively grouping together) the parent's id.parent IS NOT NULL
: Put the parent row on top of the groupid
: Finally sort all the children (same parent, andparent
is not null)
这篇关于MySql:父级和子级ORDER BY的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文