在层次查询中定义子项的排序顺序

Question

我希望您能提供一个按照sequence_within_parent列对层次查询中的子项排序的Oracle SQL查询语句。以下是示例数据集和查询：

create table tasks (task_id                 number
                    ,parent_id               number
                    ,sequence_within_parent number
                    ,task                    varchar2(30)
                    );
insert into tasks values ( 1, NULL, 0, 'Task 1');
insert into tasks values ( 2,    1, 1, 'Task 1.1');
insert into tasks values ( 3,    1, 2, 'Task 1.2');
insert into tasks values ( 4,    2, 2, 'Task 1.1.2');
insert into tasks values ( 5,    3, 1, 'Task 1.2.1');
insert into tasks values ( 6,    2, 1, 'Task 1.1.1');
insert into tasks values ( 7,    3, 4, 'Task 1.2.4');
insert into tasks values ( 8,    3, 2, 'Task 1.2.2');
insert into tasks values ( 9,    3, 3, 'Task 1.2.3');
insert into tasks values (10 ,   2, 3, 'Task 1.1.3');

column task format a30

select task_id
      ,sequence_within_parent
      ,lpad(' ', 2 * (level - 1), ' ') || task task
from   tasks
connect by parent_id = prior task_id
start with task_id = 1
/

此查询返回以下内容：

   TASK_ID SEQUENCE_WITHIN_PARENT TASK
---------- ---------------------- ---------------
         1                      0 Task 1
         2                      1   Task 1.1
         4                      2     Task 1.1.2
         6                      1     Task 1.1.1
        10                      3     Task 1.1.3
         3                      2   Task 1.2
         5                      1     Task 1.2.1
         7                      4     Task 1.2.4
         8                      2     Task 1.2.2
         9                      3     Task 1.2.3

优先输出如下，其中子元素按正确的顺序排列：

   TASK_ID SEQUENCE_WITHIN_PARENT TASK
---------- ---------------------- ---------------
         1                      0 Task 1
         2                      1   Task 1.1
         6                      1     Task 1.1.1
         4                      2     Task 1.1.2
        10                      3     Task 1.1.3
         3                      2   Task 1.2
         5                      1     Task 1.2.1
         8                      2     Task 1.2.2
         9                      3     Task 1.2.3
         7                      4     Task 1.2.4

- Tai Paul

Answer 1

需要添加到查询中的子句是“ORDER SIBLINGS BY SEQUENCE_WITHIN_PARENT”。

在层次结构中，所有子节点或子项都被称为兄弟节点。

示例数据集的完整查询为：

select rownum
      ,task_id
      ,sequence_within_parent
      ,lpad(' ', 2 * (level - 1), ' ') || task  task
from   tasks
connect by parent_id = prior task_id
start with task_id = 1
order siblings by sequence_within_parent
/

- Tai Paul

---

在非常简单的查询中，ORDER SIBLINGS BY可能会起作用。http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1154964870586有一些查询（双connect by prior）是不行的。而且它也没有给你太多的灵活性，使用零填充字符串就可以了。如果您使用的是Oracle >= 9i，则无关紧要，但是connect siblings by当时不存在。 - RichardTheKiwi

你可以在x个小时后接受自己的答案并获得声望分。同时，公正地为给你提供新视角的其他答案点赞。祝大家好运。 - shellter

Answer 2

SQL Server有一个hierarchyID类型，非常适合处理这种情况。对于其他所有关系型数据库管理系统，我通常使用以下字符串连接模拟。

select task_id
      ,sequence_within_parent
      ,lpad(' ', 2 * (level - 1), ' ') || task task
      ,SYS_CONNECT_BY_PATH(
        to_char(parent_id, 'FM000000000')
        ||
        to_char(sequence_within_parent, 'FM000000000')
        ,'/') hier
from   tasks
connect by parent_id = prior task_id
start with task_id = 1
order by hier;

- RichardTheKiwi

---

感谢您对这个主题的出色变化。为了完成最初的请求，我会添加“column hier noprint”。 - Tai Paul

来自Oracle文档的提示：“在分层查询中，不要指定ORDER BY或GROUP BY，因为它们会破坏CONNECT BY结果的分层顺序。如果您想对同一父级的兄弟行进行排序，则使用ORDER SIBLINGS BY子句。”请参见此页面的中间部分https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm - Joram