需要通过递归或连接查询获取层次结构（层级1、层级2、层级3、层级4、层级5、层级6）的结果。

Question

我的账户结构是基于树状的：

| code | Title |
| --- | --- |
| 1 | abc |
| 1-1 | kkk |
| 1-1-1 | dfgf |
| 1-1-1-1 | dfsd |
| 1-1-1-1-1-1 | sdfsd |
| 1-1-1-1-1-1-1 | sccdfsd |

在这个树状结构中有5000条记录，我需要按照层级获取结果。

| 祖母级标题 | 父级标题 | 子级标题 | ... |
| --- | --- | --- | --- |
| 层级1标题 | 层级2标题 | 层级3标题 | 层级4标题 | 层级5标题 | 层级6标题 | 层级7标题 | 层级8标题 | 层级9标题 |

如何将所有父级标题名称按层级分别显示在不同的列中？

James Kanze · Answer

您可以通过检测CODE列中定义父级级别的元素数量，并在PIVOT操作中使用它来获取各个层级的标题：

-- 示例数据：
WITH tbl AS
(
    Select '1' as "CODE", 'abc' as "TITLE" From Dual Union All
    Select '1-1' as "CODE", 'kkk' as "TITLE" From Dual Union All
    Select '1-1-1' as "CODE", 'dfgf' as "TITLE" From Dual Union All
    Select '1-1-1-1' as "CODE", 'dfsd' as "TITLE" From Dual Union All
    Select '1-1-1-1-1' as "CODE", 'sdfsd' as "TITLE" From Dual Union All
    Select '1-1-1-1-1-1' as "CODE", 'sccdfsd' as "TITLE" From Dual 
)

-- SQL查询：
Select  Max(TITLE_1) as "TITLE_1", Max(TITLE_2) as "TITLE_2", Max(TITLE_3) as "TITLE_3", 
        Max(TITLE_4) as "TITLE_4", Max(TITLE_5) as "TITLE_5", Max(TITLE_6) as "TITLE_6"
From    (
          Select    CODE, TITLE, Length(CODE) - Length(Replace(CODE, '-')) + 1 as "LVL"
          From  tbl
        )
        PIVOT(Max(TITLE) For LVL IN(1 as "TITLE_1", 2 as "TITLE_2", 3 as "TITLE_3", 
                                 4 as "TITLE_4", 5 as "TITLE_5", 6 as "TITLE_6",
                                 7 as "TITLE_7", 8 as "TITLE_8", 9 as "TITLE_9"))
Order By CODE

/* 结果：
TITLE_1   TITLE_2   TITLE_3   TITLE_4   TITLE_5   TITLE_6
-------   -------   -------   -------   -------   -------
abc       kkk       dfgf      dfsd      sdfsd     sccdfsd
*/

如果您想使用connect by，只需将内部SQL更改为如下所示：

Select  Max(TITLE_1) as "TITLE_1", Max(TITLE_2) as "TITLE_2", Max(TITLE_3) as "TITLE_3", 
        Max(TITLE_4) as "TITLE_4", Max(TITLE_5) as "TITLE_5", Max(TITLE_6) as "TITLE_6"
From    (
          Select    CODE, TITLE, LEVEL as "LVL"
          From  tbl
          Where LEVEL = Length(CODE) - Length(Replace(CODE, '-')) + 1
          Connect By LEVEL <= Length(CODE) - Length(Replace(CODE, '-')) + 1
        )
        PIVOT(Max(TITLE) For LVL IN(1 as "TITLE_1", 2 as "TITLE_2", 3 as "TITLE_3", 
                                 4 as "TITLE_4", 5 as "TITLE_5", 6 as "TITLE_6",
                                 7 as "TITLE_7", 8 as "TITLE_8", 9 as "TITLE_9"))
Order By CODE

-- 结果与上述相同。

Marko Topolnik · Answer

您可以计算code层次结构中的分隔符数量，并由此确定在层次结构中的深度，然后使用层级查询结合SYS_CONNECT_BY_PATH函数来连接行并找出所有标题：

SELECT SUBSTR(SYS_CONNECT_BY_PATH(title, ', '), 3) AS titles
FROM (
  SELECT code,
         title,
         LENGTH(code) - LENGTH(REPLACE(code, '-')) AS depth
  FROM   table_name
)
WHERE  CONNECT_BY_ISLEAF = 1
START WITH
       depth = 0
CONNECT BY
       code LIKE PRIOR code || '-%'
AND    depth = PRIOR depth + 1;

对于以下示例数据：

CREATE TABLE table_name (code, title) AS
  SELECT '1',           'abc'     FROM DUAL UNION ALL
  SELECT '1-1',         'kkk'     FROM DUAL UNION ALL
  SELECT '1-1-1',       'dfgf'    FROM DUAL UNION ALL
  SELECT '1-1-1-1',     'dfsd'    FROM DUAL UNION ALL
  SELECT '1-1-1-1-1',   'sdfsd'   FROM DUAL UNION ALL
  SELECT '1-1-1-1-1-1', 'sccdfsd' FROM DUAL;

输出结果将是：

| TITLES |
|--------|
| abc, kkk, dfgf, dfsd, sdfsd, sccdfsd |

在线示例