postgresql - 查询按 id 和 parent_id 对行进行排序

Question

我有一个包含嵌套数据的简单表，我需要对行进行id排序parent_id

这是实际的表：

select * from commission_category;

+----+-------+-----------+
| id | code  | parent_id |
+----+-------+-----------+
| -1 | " "   | null      |
|  1 | "AU"  | null      |
|  7 | "AI"  | null      |
|  8 | "AEM" | null      |
|  9 | "SPE" | null      |
| 10 | "AEN" | null      |
| 11 | "FV"  | null      |
| 13 | "PRO" | null      |
| 17 | "AF"  | 9         |
| 12 | "IND" | 1         |
| 15 | "CIV" | 9         |
| 16 | "CON" | 1         |
+----+-------+-----------+

---

这是所需的顺序：

+----+-------+-----------+
| id | code  | parent_id |
+----+-------+-----------+
| -1 | " "   | null      |
|  1 | "AU"  | null      |
| 16 | "CON" | 1         |
| 12 | "IND" | 1         |
|  7 | "AI"  | null      |
|  8 | "AEM" | null      |
|  9 | "SPE" | null      |
| 17 | "AF"  | 9         |
| 15 | "CIV" | 9         |
| 10 | "AEN" | null      |
| 11 | "FV"  | null      |
| 13 | "PRO" | null      |
+----+-------+-----------+

我需要的顺序需要在id之后按顺序订购表格parent_id。parent_id需要附近的行与之对应id。

示例：

第一行id = 1是parent_id下一行的。

|  1 | "AU"  | null      |
| 16 | "CON" | 1         |
| 12 | "IND" | 1         |

---

这是我制作的：

select * from (select * from commission_category order by id asc) subs order by subs.parent_id desc

+----+-------+-----------+
| id | code  | parent_id |
+----+-------+-----------+
| -1 | " "   | null      |
|  1 | "AU"  | null      |
|  7 | "AI"  | null      |
|  8 | "AEM" | null      |
|  9 | "SPE" | null      |
| 10 | "AEN" | null      |
| 11 | "FV"  | null      |
| 13 | "PRO" | null      |
| 17 | "AF"  | 9         |
| 15 | "CIV" | 9         |
| 16 | "CON" | 1         |
| 12 | "IND" | 1         |
+----+-------+-----------+

我在查询中遗漏了什么？

标签： postgresql

Answer 1

您需要多个级别的排序：

select * 
from commission_category
order by coalesce(parent_id, id), 
         (parent_id is null)::int desc, 
         id desc

请参阅演示。
结果：

> id | code | parent_id
> -: | :--- | --------:
> -1 |      |      null
>  1 | AU   |      null
> 16 | CON  |         1
> 12 | IND  |         1
>  7 | AI   |      null
>  8 | AEM  |      null
>  9 | SPE  |      null
> 17 | AF   |         9
> 15 | CIV  |         9
> 10 | AEN  |      null
> 11 | FV   |      null
> 13 | PRO  |      null