通过顺序字段连接的Oracle SQL分层查询

Question

我有一个分层任务的方案，比如：

|-- task_1
|---- task_1_1
|------ task_1_1_1
|------ task_1_1_2
|---- task_1_2
|------ task_1_2_1
|------ task_1_2_2
|-- task_2
etc.

每个级别的任务数量可能会随着层次结构的深度而变化。

在数据库中，它们被存储为一个序列：

parent_sequence[/tr><1>1<1><1><1><1><1><1><1><2>2

名称		序列	project_id
task_1	1
任务_1_1	2	1
任务_1_1_1	3	2
任务_1_1_2	4	2
task_1_2	5	1
任务_1_2_1	6	5
任务_1_2_2	7	5
任务2	8	-1
task_b_1	1
task_b_1_1	2	1
task_b_1_1_1	3	2	2

您可以将project_id比较添加到CONNECT BY过滤器：

SELECT project_id,
task.name AS task_name,
SYS_CONNECT_BY_PATH(task.name, '/') AS task_path
FROM   task
START WITH task.parent_sequence = -1
CONNECT BY
PRIOR task.sequence = task.parent_sequence 
AND   PRIOR project_id    = project_id
ORDER SIBLINGS BY project_id, sequence

对于样本数据：

CREATE TABLE task (name, sequence, parent_sequence, project_id) AS
SELECT 'task_1',       1, -1, 1 FROM DUAL UNION ALL
SELECT 'task_1_1',     2,  1, 1 FROM DUAL UNION ALL
SELECT 'task_1_1_1',   3,  2, 1 FROM DUAL UNION ALL
SELECT 'task_1_1_2',   4,  2, 1 FROM DUAL UNION ALL
SELECT 'task_1_2',     5,  1, 1 FROM DUAL UNION ALL
SELECT 'task_1_2_1',   6,  5, 1 FROM DUAL UNION ALL
SELECT 'task_1_2_2',   7,  5, 1 FROM DUAL UNION ALL
SELECT 'task_2',       8, -1, 1 FROM DUAL UNION ALL
SELECT 'task_b_1',     1, -1, 2 FROM DUAL UNION ALL
SELECT 'task_b_1_1',   2,  1, 2 FROM DUAL UNION ALL
SELECT 'task_b_1_1_1', 3,  2, 2 FROM DUAL;

输出：

/task_1/task_1_1_11/task_1_1_1/task_1/task_1_2/task_1_1_2_1/task_1/task_1_2/task_1_2/task_2/task_b_1/task_b_1/task_b_1/task_b_1/task_b_1

PROJECT_ID	TASK_NAME	TASK_PATH
1	task_1
1		task_1_1	/task_1/task_1_1_1
1	task_1_1_1
1		task_1_1_2	/task_1/task_1_1_1_2
1		task_1_2	/task_1/task_1_2
1	task_1_2_1
1	task_1_2_2
1	task_2
2	task_b_1
2	task_b_1_1
2	task_b_1_1