分层 SQL 作为链表

Question

>我有一个数据集，它表示一组创建一组产品的流程步骤。 每个产品都有一个在数据集中定义的步骤序列，即 FromStep 和 ToStep。 我正在尝试使用分层查询来提取所有产品的流程步骤，但我显然缺少一些东西，因为它不起作用。 任何关于我哪里出错的建议都将不胜感激(也许这甚至不是处理这项任务的方法)。 我试图在下面创建一个我的问题的最小示例 - 我的真实数据集比这大得多。

创建示例表：

CREATE TABLE HIER_TEST    
(      
PRODUCT     VARCHAR2(26 BYTE),    
STEPNAME    VARCHAR2(26 BYTE),    
STEPID      NUMBER(4,0),    
FROMSTEP    NUMBER(4,0),    
TOSTEP      NUMBER(4,0)   
) ;   

添加一些数据：

Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product1','Step1',1,1,2);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product1','Step2',2,2,3);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product1','Step3',3,3,4);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product1','Step4',4,4,5);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product1','Step5',5,5,6);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product2','Step1',1,1,2);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product2','Step2',2,2,3);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product2','Step3',3,3,4);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product2','Step4',4,4,5);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product2','Step5',5,5,6);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product5','Step1',1,1,2);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product5','Step2',2,2,3);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product5','Step3',3,3,4);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product5','Step4',4,4,5);   
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product5','Step5',5,5,6);   

然后我正在尝试这个查询：

select    
level,   
connect_by_isleaf leafnode,   
hier_test.*,   
connect_by_root stepid as rootItem   
from   
hier_test       
start with   
stepid = 1   
connect by nocycle prior   
tostep = fromstep   
order siblings by   
product,   
stepid   
;   

返回：

+-------+-------+--------------+-----------+-------+-------+-------+-------+   
| Level | LfNd  |   Product    | StepName  | StepId| FrStp | ToStp | rtItm |   
+-------+-------+--------------+-----------+-------+-------+-------+-------+   
|   1   |   0   |   Product1   |   Step1   |   1   |   1   |   2   |   1   |   
|   2   |   0   |   Product1   |   Step2   |   2   |   2   |   3   |   1   |   
|   3   |   0   |   Product1   |   Step3   |   3   |   3   |   4   |   1   |   
|   4   |   0   |   Product1   |   Step4   |   4   |   4   |   5   |   1   |   
|   5   |   1   |   Product1   |   Step5   |   5   |   5   |   6   |   1   |   
|   5   |   1   |   Product2   |   Step5   |   5   |   5   |   6   |   1   |   
|   5   |   1   |   Product5   |   Step5   |   5   |   5   |   6   |   1   |   
|   4   |   0   |   Product2   |   Step4   |   4   |   4   |   5   |   1   |   
|   5   |   1   |   Product1   |   Step5   |   5   |   5   |   6   |   1   |   
|   5   |   1   |   Product2   |   Step5   |   5   |   5   |   6   |   1   |   
|   5   |   1   |   Product5   |   Step5   |   5   |   5   |   6   |   1   |   
|   4   |   0   |   Product5   |   Step4   |   4   |   4   |   5   |   1   |   
|   5   |   1   |   Product1   |   Step5   |   5   |   5   |   6   |   1   |   
|   5   |   1   |   Product2   |   Step5   |   5   |   5   |   6   |   1   |   
|   5   |   1   |   Product5   |   Step5   |   5   |   5   |   6   |   1   |   
|   3   |   0   |   Product2   |   Step3   |   3   |   3   |   4   |   1   |   
|   4   |   0   |   Product1   |   Step4   |   4   |   4   |   5   |   1   |   
|   5   |   1   |   Product1   |   Step5   |   5   |   5   |   6   |   1   |   
|   5   |   1   |   Product2   |   Step5   |   5   |   5   |   6   |   1   |   
|   5   |   1   |   Product5   |   Step5   |   5   |   5   |   6   |   1   |   
This appears to be locked in a loop forever...

我期待按子句排序兄弟姐妹按产品然后步骤号对表进行排序，但结果没有显示这一点。

我的目标是得到这个：

+-------+-------+--------------+-----------+-------+-------+-------+-------+   
| Level | LfNd  |   Product    | StepName  | StepId| FrStp | ToStp | rtItm |   
+-------+-------+--------------+-----------+-------+-------+-------+-------+   
|   1   |   0   |   Product1   |   Step1   |   1   |   1   |   2   |   1   |   
|   2   |   0   |   Product1   |   Step2   |   2   |   2   |   3   |   1   |   
|   3   |   0   |   Product1   |   Step3   |   3   |   3   |   4   |   1   |   
|   4   |   0   |   Product1   |   Step4   |   4   |   4   |   5   |   1   |   
|   5   |   1   |   Product1   |   Step5   |   5   |   5   |   6   |   1   |   
|   1   |   0   |   Product2   |   Step1   |   1   |   1   |   2   |   1   |   
|   2   |   0   |   Product2   |   Step2   |   2   |   2   |   3   |   1   |   
|   3   |   0   |   Product2   |   Step3   |   3   |   3   |   4   |   1   |   
|   4   |   0   |   Product2   |   Step4   |   4   |   4   |   5   |   1   |   
|   5   |   1   |   Product2   |   Step5   |   5   |   5   |   6   |   1   |   
|   1   |   0   |   Product5   |   Step1   |   1   |   1   |   2   |   1   |   
|   2   |   0   |   Product5   |   Step2   |   2   |   2   |   3   |   1   |   
|   3   |   0   |   Product5   |   Step3   |   3   |   3   |   4   |   1   |   
|   4   |   0   |   Product5   |   Step4   |   4   |   4   |   5   |   1   |   
|   5   |   1   |   Product5   |   Step5   |   5   |   5   |   6   |   1   |   

我相信这将是显而易见的，但是在尝试找到解决方案时，我没有发现任何真正对我有帮助的东西。

提前谢谢。

Answer 1

结果显示了这一点。

CREATE TABLE HIER_TEST    
(      
PRODUCT     VARCHAR2(26 BYTE),    
STEPNAME    VARCHAR2(26 BYTE),    
STEPID      NUMBER(4,0),    
FROMSTEP    NUMBER(4,0),    
TOSTEP      NUMBER(4,0)   
);

获取较小的数据子集，以便更轻松地可视化：

Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP)
SELECT 'Product1','Step1',1,1,2 FROM DUAL UNION ALL   
SELECT 'Product1','Step2',2,2,3 FROM DUAL UNION ALL   
SELECT 'Product1','Step3',3,3,4 FROM DUAL UNION ALL
SELECT 'Product2','Step1',1,1,2 FROM DUAL UNION ALL   
SELECT 'Product2','Step2',2,2,3 FROM DUAL UNION ALL   
SELECT 'Product2','Step3',3,3,4 FROM DUAL UNION ALL
SELECT 'Product5','Step1',1,1,2 FROM DUAL UNION ALL   
SELECT 'Product5','Step2',2,2,3 FROM DUAL UNION ALL   
SELECT 'Product5','Step3',3,3,4 FROM DUAL;

然后添加SYS_CONNECT_BY_PATH( Product, ', ' )查询(您不需要NOCYCLE子句，因为数据中没有任何循环)：

select level,   
connect_by_isleaf leaf,
product,
stepname AS sname,
stepid AS id,
fromstep AS fs,
tostep AS ts,
connect_by_root stepid as rt,
SYS_CONNECT_BY_PATH( product, ', ' ) As path
from   hier_test       
start with
stepid = 1   
connect by
prior tostep = fromstep   
order siblings by   
product,   
stepid

给出输出：

级别 |叶子 |产品展示 |SNAME |身份证 |金融服务 |TS |室温 |路径                          ----: |---: |:------- |:---- |-: |-: |-: |-: |:----------------------------- 1 |   0 |产品1 |步骤1 | 1 | 1 | 2 | 1 |，产品 1                    2 |   0 |产品1 |步骤2 | 2 | 2 | 3 | 1 |、产品 1、产品 1          3 |   1 |产品1 |步骤3 | 3 | 3 | 4 | 1 |、产品 1、产品 1、产品 1 3 |   1 |产品2 |步骤3 | 3 | 3 | 4 | 1 |、产品 1、产品 1、产品 2 3 |   1 |产品5 |步骤3 | 3 | 3 | 4 | 1 |、产品 1、产品 1、产品 5 2 |   0 |产品2 |步骤2 | 2 | 2 | 3 | 1 |、产品 1、产品 2          3 |   1 |产品1 |步骤3 | 3 | 3 | 4 | 1 |、产品 1、产品 2、产品 1 3 |   1 |产品2 |步骤3 | 3 | 3 | 4 | 1 |、产品 1、产品 2、产品 2 3 |   1 |产品5 |步骤3 | 3 | 3 | 4 | 1 |、产品 1、产品 2、产品 5 2 |   0 |产品5 |步骤2 | 2 | 2 | 3 | 1 |、产品 1、产品 5          3 |   1 |产品1 |步骤3 | 3 | 3 | 4 | 1 |、产品 1、产品 5、产品 1 3 |   1 |产品2 |步骤3 | 3 | 3 | 4 | 1 |、产品 1、产品 5、产品 2 3 |   1 |产品5 |步骤3 | 3 | 3 | 4 | 1 |、产品 1、产品 5、产品 5 1 |   0 |产品2 |步骤1 | 1 | 1 | 2 | 1 |，产品2                    2 |   0 |产品1 |步骤2 | 2 | 2 | 3 | 1 |、产品 2、产品 1          3 |   1 |产品1 |步骤3 | 3 | 3 | 4 | 1 |、产品 2、产品 1、产品 1 3 |   1 |产品2 |步骤3 | 3 | 3 | 4 | 1 |、产品 2、产品 1、产品 2 3 |   1 |产品5 |步骤3 | 3 | 3 | 4 | 1 |、产品 2、产品 1、产品 5 2 |   0 |产品2 |步骤2 | 2 | 2 | 3 | 1 |、产品 2、产品 2          3 |   1 |产品1 |步骤3 | 3 | 3 | 4 | 1 |、产品 2、产品 2、产品 1 3 |   1 |产品2 |步骤3 | 3 | 3 | 4 | 1 |、产品 2、产品 2、产品 2 3 |   1 |产品5 |步骤3 | 3 | 3 | 4 | 1 |、产品 2、产品 2、产品 5 2 |   0 |产品5 |步骤2 | 2 | 2 | 3 | 1 |、产品 2、产品 5          3 |   1 |产品1 |步骤3 | 3 | 3 | 4 | 1 |、产品 2、产品 5、产品 1 3 |   1 |产品2 |步骤3 | 3 | 3 | 4 | 1 |、产品 2、产品 5、产品 2 3 |   1 |产品5 |步骤3 | 3 | 3 | 4 | 1 |、产品 2、产品 5、产品 5 1 |   0 |产品5 |步骤1 | 1 | 1 | 2 | 1 |，产品5                    2 |   0 |产品1 |步骤2 | 2 | 2 | 3 | 1 |、产品 5、产品 1          3 |   1 |产品1 |步骤3 | 3 | 3 | 4 | 1 |、产品 5、产品 1、产品 1 3 |   1 |产品2 |步骤3 | 3 | 3 | 4 | 1 |、产品 5、产品 1、产品 2 3 |   1 |产品5 |步骤3 | 3 | 3 | 4 | 1 |、产品 5、产品 1、产品 5 2 |   0 |产品2 |步骤2 | 2 | 2 | 3 | 1 |、产品 5、产品 2          3 |   1 |产品1 |步骤3 | 3 | 3 | 4 | 1 |、产品 5、产品 2、产品 1 3 |   1 |产品2 |步骤3 | 3 | 3 | 4 | 1 |、产品 5、产品 2、产品 2 3 |   1 |产品5 |步骤3 | 3 | 3 | 4 | 1 |、产品 5、产品 2、产品 5 2 |   0 |产品5 |步骤2 | 2 | 2 | 3 | 1 |、产品 5、产品 5          3 |   1 |产品1 |步骤3 | 3 | 3 | 4 | 1 |、产品 5、产品 5、产品 1 3 |   1 |产品2 |步骤3 | 3 | 3 | 4 | 1 |、产品 5、产品 5、产品 23 |   1 |产品5 |步骤3 | 3 | 3 | 4 | 1 |

、产品 5、产品 5、产品 5然后查看路径，您可以看到它由同级在第一级排序，然后在第二级，然后在第三级排序。这是因为您只是在当前深度上查看产品(等)，而您没有看到此排序。

如果要将其限制为具有与上一个级别相同的产品，请将其添加到CONNECT BY子句中：

select level,   
connect_by_isleaf leaf,
product,
stepname AS sname,
stepid AS id,
fromstep AS fs,
tostep AS ts,
connect_by_root stepid as rt,
SYS_CONNECT_BY_PATH( product, ', ' ) As path
from   hier_test       
start with
stepid = 1   
connect by
prior tostep  = fromstep
and    prior product = product
order siblings by   
product,   
stepid

哪些输出：

级别 |叶子 |产品展示 |SNAME |身份证 |金融服务 |TS |室温 |路径                          ----: |---: |:------- |:---- |-: |-: |-: |-: |:----------------------------- 1 |   0 |产品1 |步骤1 | 1 | 1 | 2 | 1 |，产品 1                    2 |   0 |产品1 |步骤2 | 2 | 2 | 3 | 1 |、产品 1、产品 1          3 |   1 |产品1 |步骤3 | 3 | 3 | 4 | 1 |、产品 1、产品 1、产品 1 1 |   0 |产品2 |步骤1 | 1 | 1 | 2 | 1 |，产品2                    2 |   0 |产品2 |步骤2 | 2 | 2 | 3 | 1 |、产品 2、产品 2          3 |   1 |产品2 |步骤3 | 3 | 3 | 4 | 1 |、产品 2、产品 2、产品 2 1 |   0 |产品5 |步骤1 | 1 | 1 | 2 | 1 |，产品5                    2 |   0 |产品5 |步骤2 | 2 | 2 | 3 | 1 |、产品 5、产品 5          3 |   1 |产品5 |步骤3 | 3 | 3 | 4 | 1 |

， 产品 5， 产品 5， 产品 5db<> 小提琴