MariaDB按ID降序实现树形结构排序的SQL求解

现有MariaDB数据表如下：

+----+-----------+--------------+--------+----------+
| id | firstName | favoriteFood | status | parentID |
+----+-----------+--------------+--------+----------+
| 1  |   Andy    |    pizza     | child  |    5     |
| 2  |   Alice   |    burger    | child  |    5     |
| 3  |    Bob    |    fries     | orphan |   null   |
| 4  |  Barney   |    salad     | parent |   null   |
| 5  |  Christ   |    steak     | parent |   null   |
| 6  |  Daniel   |    pizza     | child  |    8     |
| 7  |   Mike    |    fries     | child  |    5     |
| 8  |  Richard  |   oatmeal    | parent |   null   |
| 9  |  Wilson   |    steak     | child  |    8     |
| 10 |   Dicky   |  watermelon  | orphan |   null   |
| 11 |   Freya   |    potato    | orphan |   null   |
| 12 |   Ryan    |    oyster    | parent |   null   |
| 13 |   Alex    |    bread     | orphan |   null   |
| 14 |   Sarah   |   brocoli    | child  |    12    |
| 15 |   Dane    |    toast     | child  |    8     |
+----+-----------+--------------+--------+----------+

需求：按id降序排序，同时子项（child）必须始终位于对应父项（parent）的下方。

尝试执行以下SQL未得到预期结果：

SELECT * from table ORDER BY id DESC, FIELD(status,'parent','child'), parentID

预期结果：

+----+-----------+--------------+--------+----------+
| id | firstName | favoriteFood | status | parentID |
+----+-----------+--------------+--------+----------+
| 13 |   Alex    |    bread     | orphan |   null   |
| 12 |   Ryan    |    oyster    | parent |   null   |
| 14 |   Sarah   |   brocoli    | child  |    12    |
| 11 |   Freya   |    potato    | orphan |   null   |
| 10 |   Dicky   |  watermelon  | orphan |   null   |
| 8  |  Richard  |   oatmeal    | parent |   null   |
| 15 |   Dane    |    toast     | child  |    8     |
| 9  |  Wilson   |    steak     | child  |    8     |
| 6  |  Daniel   |    pizza     | child  |    8     |
| 5  |  Christ   |    steak     | parent |   null   |
| 7  |   Mike    |    fries     | child  |    5     |
| 2  |   Alice   |    burger    | child  |    5     |
| 1  |   Andy    |    pizza     | child  |    5     |
| 4  |  Barney   |    salad     | parent |   null   |
| 3  |    Bob    |    fries     | orphan |   null   |
+----+-----------+--------------+--------+----------+

SELECT *
FROM your_table_name
ORDER BY 
    COALESCE(parentID, id) DESC,
    FIELD(status, 'parent', 'child', 'orphan'),
    id DESC;

逻辑说明 #

1. COALESCE(parentID, id) DESC：为每个分组确定排序基准——父项和其子项使用父项的id作为排序键，孤儿项使用自身id。这样父项和子项会被归为一组，整体按基准id降序排列，保证父项组在对应子项组之前的大顺序符合id降序要求。
2. FIELD(status, 'parent', 'child', 'orphan')：在同一分组内，强制父项排在子项前面，孤儿项单独排序（这里孤儿项因为基准是自身id，不会和其他组混合）。
3. id DESC：同一状态的项（比如同属一个父项的子项）按自身id降序排列，和整体排序规则保持一致。

注意：将SQL中的your_table_name替换为实际的数据表名称。

内容的提问来源于stack exchange，提问作者Evelyn Woodley