时间戳

首页 > 解决方案 > 获取Oracle分层查询中的所有子路径

sql - 获取Oracle分层查询中的所有子路径

问题描述

我有一个包含销售部门列表的表格，如下所示：

+-------+---------------+-----------+
|DEPT_ID|DEPT_NAME      |DEPT_PARENT|
+-------+---------------+-----------+
|5500   |World          |           |
|5510   |Region 1       |5500       |
|5511   |Cell 1 Region 1|5510       |
|5512   |Cell 2 Region 1|5510       |
|5513   |Cell 3 Region 1|5510       |
|5514   |Cell 4 Region 1|5510       |
|5515   |Cell 5 Region 1|5510       |
|5520   |Region 2       |5500       |
|5521   |Cell 1 Region 2|5520       |
|5522   |Cell 2 Region 2|5520       |
|5530   |Region 3       |5500       |
|5531   |Cell 1 Region 3|5530       |
|5532   |Cell 2 Region 3|5530       |
|5540   |Region 4       |5500       |
|5533   |Cell 1 Region 4|5540       |
|5534   |Cell 2 Region 4|5533       |
|5590   |Region 5       |5500       |
|5591   |Cell 1 Region 5|5590       |
+-------+---------------+-----------+

我需要一个返回所有可能子路径的查询。因此，对于前三行，它应该如下所示：

5500 5510
5500 5511
5510 5511

因此对于每个可能的子路径，它将返回路径的第一个和最后一个部门。通过这样做很容易获得路径：

    SELECT d.*, LTRIM (SYS_CONNECT_BY_PATH (dept_id, '-'), '-') AS PATH
      FROM depts d
START WITH dept_parent IS NULL
CONNECT BY PRIOR dept_id = dept_parent

但我怎样才能获得所有可能的子路径？

标签： sqloraclehierarchyhierarchical-data

解决方案

用于CONNECT_BY_ROOT查找路径的起点。

甲骨文设置：

CREATE TABLE depts ( DEPT_ID, DEPT_NAME, DEPT_PARENT ) AS
SELECT 5500, 'World',           NULL FROM DUAL UNION ALL
SELECT 5510, 'Region 1',        5500 FROM DUAL UNION ALL
SELECT 5511, 'Cell 1 Region 1', 5510 FROM DUAL UNION ALL
SELECT 5512, 'Cell 2 Region 1', 5510 FROM DUAL UNION ALL
SELECT 5513, 'Cell 3 Region 1', 5510 FROM DUAL UNION ALL
SELECT 5514, 'Cell 4 Region 1', 5510 FROM DUAL UNION ALL
SELECT 5515, 'Cell 5 Region 1', 5510 FROM DUAL UNION ALL
SELECT 5520, 'Region 2',        5500 FROM DUAL UNION ALL
SELECT 5521, 'Cell 1 Region 2', 5520 FROM DUAL UNION ALL
SELECT 5522, 'Cell 2 Region 2', 5520 FROM DUAL UNION ALL
SELECT 5530, 'Region 3',        5500 FROM DUAL UNION ALL
SELECT 5531, 'Cell 1 Region 3', 5530 FROM DUAL UNION ALL
SELECT 5532, 'Cell 2 Region 3', 5530 FROM DUAL UNION ALL
SELECT 5540, 'Region 4',        5500 FROM DUAL UNION ALL
SELECT 5533, 'Cell 1 Region 4', 5540 FROM DUAL UNION ALL
SELECT 5534, 'Cell 2 Region 4', 5533 FROM DUAL UNION ALL
SELECT 5590, 'Region 5',        5500 FROM DUAL UNION ALL
SELECT 5591, 'Cell 1 Region 5', 5590 FROM DUAL;

查询：

SELECT CONNECT_BY_ROOT( dept_parent ) AS ancestor,
       dept_id,
       SYS_CONNECT_BY_PATH( dept_parent, '>' ) || '>' || dept_id AS path
FROM   depts
START WITH dept_parent IS NOT NULL
CONNECT BY PRIOR dept_id = dept_parent;

输出：

祖先 | 部门 ID | 小路                
--------: | ------: | :--------------------
    5500 | 5510 | >5500>5510          
    5500 | 5511 | >5500>5510>5511     
    5500 | 5512 | >5500>5510>5512     
    5500 | 5513 | >5500>5510>5513     
    5500 | 5514 | >5500>5510>5514     
    5500 | 5515 | >5500>5510>5515     
    5500 | 5520 | >5500>5520          
    5500 | 5521 | >5500>5520>5521     
    5500 | 5522 | >5500>5520>5522     
    5500 | 5530 | >5500>5530          
    5500 | 5531 | >5500>5530>5531     
    5500 | 5532 | >5500>5530>5532     
    5500 | 5540 | >5500>5540          
    5500 | 5533 | >5500>5540>5533     
    5500 | 5534 | >5500>5540>5533>5534
    5500 | 5590 | >5500>5590          
    5500 | 5591 | >5500>5590>5591     
    5510 | 5511 | >5510>5511          
    5510 | 5512 | >5510>5512          
    5510 | 5513 | >5510>5513          
    5510 | 5514 | >5510>5514          
    5510 | 5515 | >5510>5515          
    5520 | 5521 | >5520>5521          
    5520 | 5522 | >5520>5522          
    5530 | 5531 | >5530>5531          
    5530 | 5532 | >5530>5532          
    5533 | 5534 | >5533>5534          
    5540 | 5533 | >5540>5533          
    5540 | 5534 | >5540>5533>5534     
    5590 | 5591 | >5590>5591

db<>在这里摆弄

推荐阅读