Oracle sql - 分层查询

debugcn 发表于 Dev

贾克斯

我正在努力对下表进行分层查询。

CLASSSTRUCTUREID  PARENT    CLASSIFCATIONID
1688                             FLT
1689              1688           ASSET
1690              1688           PMFLT
1691              1688           CM
1692              1691           POSTFAILCM

例如，对于上面的示例，我想获得如下输出

FLT/CM/POSTFAILCM as FLT is the parent of CM and CM is the parent of POSTFAILCM

我使用了以下查询，但没有给我所需的输出。我只是想知道我哪里做错了。

SELECT LPAD(' ', 2*level-1)|| SYS_CONNECT_BY_PATH(CLASSIFICATIONID , '/') "Path"
,LEVEL
   FROM CLASSSTRUCTURE
   START WITH CLASSIFICATIONID = 'PREFAILCM' --TRIED 'FLT' TOO
   CONNECT BY PRIOR  TO_CHAR(CLASSSTRUCTUREUID) = PARENT

比卡什·兰詹·博伊

希望这可以帮助：

SELECT    LPAD (' ', 2 * LEVEL - 1) || SYS_CONNECT_BY_PATH (CLASSIFICATIONID, '/') PATH,
           LEVEL
      FROM CLASSSTRUCTURE
START WITH parent IS NULL
CONNECT BY PRIOR TO_CHAR (CLASSSTRUCTUREID) = PARENT;

使用创建表：

CREATE TABLE CLASSSTRUCTURE AS
(select 1688 CLASSSTRUCTUREID, NULL PARENT,'FLT' CLASSIFICATIONID FROM DUAL UNION ALL
select 1689 CLASSSTRUCTUREID, 1688 ,'ASSET' FROM DUAL UNION ALL
select 1690 CLASSSTRUCTUREID, 1688 ,'PMFLT' FROM DUAL UNION ALL
select 1691 CLASSSTRUCTUREID, 1688 ,'CM' FROM DUAL UNION ALL
select 1692 CLASSSTRUCTUREID, 1691 ,'POSTFAILCM' FROM DUAL );

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。