mysql 父子排序_MySql：ORDER BY父子

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?

解决方案

Including sorting children by id:

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

Explanation:

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 group

id: Finally sort all the children (same parent, and parent is not null)