在保持行的层次结构的同时对表进行排序

Question

我有一张表，代表各部门的等级：

+-----------+--------------+--------------+--------------+-----------+-------+
| Top Dept. | 2-tier Dept. | 3-tire Dept. | 4-tier Dept. |    name   |  tier |
+-----------+--------------+--------------+--------------+-----------+-------+
|        00 |              |              |              |    abc    |   0   |
|           | 00-01        |              |              |    bcd    |   1   |
|           |              | 00-01-01     |              |    cde    |   2   |
|           |              | 00-01-02     |              |    abc    |   2   |
|           | 00-02        |              |              |    aef    |   1   |
|           |              | 00-02-01     |              |    qwe    |   2   |
|           |              | 00-02-03     |              |    abc    |   2   |
|           |              |              | 00-02-03-01  |    abc    |   3   |
+-----------+--------------+--------------+--------------+-----------+-------+

现在，我想按它们的名称对同层中的行进行排序，同时保持总体的层次结构，这就是我所期望的：

+-----------+--------------+--------------+--------------+-----------+-------+
| Top Dept. | 2-tier Dept. | 3-tire Dept. | 4-tier Dept. |    name   |  tier |
+-----------+--------------+--------------+--------------+-----------+-------+
|        00 |              |              |              |    abc    |   0   |
|           | 00-02        |              |              |    aef    |   1   |
|           |              | 00-02-03     |              |    abc    |   2   |
|           |              | 00-02-01     |              |    qwe    |   2   |
|           | 00-01        |              |              |    def    |   1   |
|           |              | 00-01-02     |              |    abc    |   2   |
|           |              | 00-01-01     |              |    cde    |   2   |
|           |              |              | 00-02-03-01  |    abc    |   3   |
+-----------+--------------+--------------+--------------+-----------+-------+

丢失的数据意味着null，我使用的是Oracle DB，有人能帮我吗？

编辑:实际上，它是此sql的一个简单版本，我尝试添加一个新列，它连接前四列的值，然后按它和名称排序，但是它没有工作。

Answer 1

更新:这看起来很有效..。SQL Fiddle

从我最初的评论中，真正需要的是在两个选择中按照这个顺序将名称修改为部门。这允许引擎先按名称排序，同时维护层次结构。

WITH cte(Dept, superiorDept, name, depth, sort)AS (
SELECT 
  Dept,
  superiorDept,
  name,
  0,
  name|| dept
FROM hierarchy h
WHERE superiorDept IS NULL

UNION ALL

SELECT 
  h2.Dept,
  h2.superiorDept,
  h2.name,
  cte.depth + 1,
  cte.sort || h2.name ||h2.dept
FROM hierarchy h2
INNER JOIN cte ON h2.superiorDept = cte.Dept
)

SELECT 
  CASE WHEN depth = 0 THEN Dept END AS 一级部门,
  CASE WHEN depth = 1 THEN Dept END AS 二级部门,
  CASE WHEN depth = 2 THEN Dept END AS 三级部门,
  CASE WHEN depth = 3 THEN Dept END AS 四级部门,
  name,
  depth,
  sort
FROM cte
ORDER BY sort, name