为树状数据集创建递归查询

Question

| Col1      | Col2  | Col3      | Col4  | Col5
--------------------------------------------------
| B1        | A1    | somedata  | data  | somedata
| B2        | A1    | somedata  | data  | somedata
| B3        | A1    | null      | data  | somedata
| C1        | B1    | null      | data  | somedata
| C2        | B1    | 1         | data  | somedata
| D1        | C1    | 1         | data  | somedata
| D2        | C1    | 4         | data  | somedata
| D3        | C1    | 4         | data  | somedata
| Z1        | K1    | A         | B     | C
| Z1        | K2    | A         | B     | C

                    A1
                /                  
              B1        B2          B3
            /   
          C1    C2
    /      
    D1   D2  D3

我有以下表格结构。Col1 拥有 Col2 的孩子。其他列具有其他相关数据。该表包含树状结构的数据。是否可以创建一个查询来提供 A1 的所有子项。在这种情况下，它将是 B1 B2 B3 C1 C2 D1 D2 D3

我只熟悉简单的联接，不知道如何查询数据。任何帮助将不胜感激。

Answer 1

不应该太复杂：

SQL> with test (col1, col2) as
  2    (select 'B1', 'A1' from dual union all
  3     select 'B2', 'A1' from dual union all
  4     select 'B3', 'A1' from dual union all
  5     select 'C1', 'B1' from dual union all
  6     select 'C2', 'B1' from dual union all
  7     select 'D1', 'C1' from dual union all
  8     select 'D2', 'C1' from dual union all
  9     select 'D3', 'C1' from dual union all
 10     select 'Z1', 'K1' from dual union all
 11     select 'Z1', 'K2' from dual
 12    )
 13  select col1
 14  from test
 15  start with col2 = 'A1'
 16  connect by prior col1 = col2
 17  order by col1;
CO
--
B1
B2
B3
C1
C2
D1
D2
D3
8 rows selected.
SQL>