代码之家 › 专栏 › 技术社区 › Jérôme MEVEL

检索每个元素的主父级

recursive-query hierarchical-data parent-child oracle

Jérôme MEVEL · 技术社区 · 7 年前

我有一个简单的问题:

德皮德表名, dep.parent_dep_id,, EMP.现场位置来自部门部门部门 dep.dep_id=emp.dep_id上的内部联接员工emp 按部门ID排序; < /代码>

这将返回有关部门的信息,其中一些行具有相同的dep_id=,dep_name.和parent_dep_name.。这是预期的结果,因为有些员工属于同一个部门,但具有不同的site_location>code>

我想要的是添加另一列,用母版的dep-i d 我称之为master parent是指被引用为parent_dep_id但实际上不存在(在dep_id列中缺少值)。因此,所有这些行实际上都应该有一个master_parent_id->code>value等于dep_2000->code>。这只是一个示例数据,但实际上,许多行将具有不同的master_parent_id>code>。并非所有这些值都具有相同的值。要做到这一点,对于每一行,我需要执行一个递归查询,以便在树中一直搜索,直到找到一个没有任何匹配的parent_dep_id->code>value。我试图阅读和理解Oracle文档和示例,但我找不到在我的案例中工作的内容。我是否应该使用类似于connect by prior的方法来执行这种递归函数? 总的来说,SQL不是我的茶,更不是甲骨文。我找不到如何解决这个问题。谢谢你


   
    
     
      
     
    
    
    
    
     这将返回有关
     
      departments
     
     一些行有相同的
     
      DEP_ID
     
     ,
     
      DEP_NAME
     
     和
     
      PARENT_DEP_NAME
     
     . 这是预期的结果,因为有些员工属于同一个部门,但有不同的
     
      SITE_LOCATION
     
    
    
     我想再加一列
     
      MASTER_PARENT_ID
     
     与
     
      德皮特
     
     母公司的。
    
    
     我称之为主父级的是引用为
     
      PARENT_DEP_ID
     
     但实际上不存在(在
     
      德皮特
     
     列)。
    
    
     所以所有这些行实际上应该有一个
     
      主\父\ ID
     
     价值等于
     
      DEP_2000
     
     . 这只是一个示例数据,但实际上,许多行都有不同的
     
      主\父\ ID
     
     . 不是所有的都有相同的价值。
    
    
     要做到这一点,对于每一行,我需要执行一个递归查询,以便在树中一直搜索,直到找到一个
     
      父本
     
     没有任何匹配的值
     
      德皮特
     
     .
    
    
     我试图阅读和理解Oracle文档和示例,但我找不到在我的案例中工作的内容。我应该用像这样的东西吗
     
      CONNECT BY PRIOR
     
     执行这样的递归函数?
    
    
     总的来说,SQL不是我的茶,更不是甲骨文。我找不到解决这个问题的方法。
    
    
     谢谢你

2 回复 | 直到 7 年前

Alex Poole 7 年前

如果“部门”表中根本不存在主部门ID,而不是因为没有直接员工而不显示在结果中,例如,则可以使用分层查询来获取每个部门的根父项:

select dep_id, dep_name, parent_dep_id,
  connect_by_root(parent_dep_id) as master_parent_id
from department
connect by parent_dep_id = prior dep_id
start with parent_dep_id in (
  select parent_dep_id from department d1
  where not exists (
    select *
    from department d2
    where d2.dep_id = d1.parent_dep_id
  )
);

DEP_ID   DEP_NAME        PARENT_D MASTER_P
-------- --------------- -------- --------
DEP_2400 Department 2400 DEP_2000 DEP_2000
DEP_2410 Department 2410 DEP_2400 DEP_2000
DEP_2420 Department 2420 DEP_2400 DEP_2000

然后在主查询中将其用作内联视图,而不是直接引用表:

SELECT DISTINCT 
dep.DEP_ID,
dep.DEP_NAME,
dep.PARENT_DEP_ID,
emp.SITE_LOCATION,
dep.MASTER_PARENT_ID
from (
  select dep_id, dep_name, parent_dep_id,
    connect_by_root(parent_dep_id) as master_parent_id
  from department
  connect by parent_dep_id = prior dep_id
  start with parent_dep_id in (
    select parent_dep_id from department d1
    where not exists (
      select *
      from department d2
      where d2.dep_id = d1.parent_dep_id
    )
  )
) dep
INNER JOIN EMPLOYEE emp ON dep.DEP_ID = emp.DEP_ID
ORDER BY dep.DEP_ID;

DEP_ID   DEP_NAME        PARENT_D SITE_LO MASTER_P
-------- --------------- -------- ------- --------
DEP_2400 Department 2400 DEP_2000 SITE_01 DEP_2000
DEP_2400 Department 2400 DEP_2000 SITE_02 DEP_2000
DEP_2410 Department 2410 DEP_2400 SITE_01 DEP_2000
DEP_2410 Department 2410 DEP_2400 SITE_02 DEP_2000
DEP_2420 Department 2420 DEP_2400 SITE_01 DEP_2000
DEP_2420 Department 2420 DEP_2400 SITE_02 DEP_2000

这似乎更自然 DEP_2000 但要实际存在,并且它没有父级;如果事实如此,则内联视图更简单:

SELECT DISTINCT 
dep.DEP_ID,
dep.DEP_NAME,
dep.PARENT_DEP_ID,
emp.SITE_LOCATION,
dep.MASTER_PARENT_ID
from (
  select dep_id, dep_name, parent_dep_id,
    connect_by_root(dep_id) as master_parent_id
  from department
  connect by parent_dep_id = prior dep_id
  start with parent_dep_id is null
) dep
INNER JOIN EMPLOYEE emp ON dep.DEP_ID = emp.DEP_ID
ORDER BY dep.DEP_ID;

注意,还有 start with 只查找空父级的子句, connect_by_root() 正在查看 dep_id 而不是 parent_dep_id (将为空)。

MT0 7 年前

你可以使用 CONNECT_BY_ISLEAF 伪列查找层次结构树的叶,然后使用 CONNECT_BY_ROOT( ... ) 函数在开始导航树时获取值:

SQL Fiddle

Oracle 11g R2架构设置 :

CREATE TABLE DEPARTMENT( DEP_ID, DEP_NAME, PARENT_DEP_ID ) AS
SELECT 'DEP_2000', 'Dep0', NULL       FROM DUAL UNION ALL
SELECT 'DEP_2400', 'Dep1', 'DEP_2000' FROM DUAL UNION ALL
SELECT 'DEP_2410', 'Dep2', 'DEP_2400' FROM DUAL UNION ALL
SELECT 'DEP_2420', 'Dep3', 'DEP_2400' FROM DUAL;

查询1 :

SELECT CONNECT_BY_ROOT( DEP_ID )        AS DEP_ID,
       CONNECT_BY_ROOT( DEP_NAME )      AS DEP_NAME,
       CONNECT_BY_ROOT( PARENT_DEP_ID ) AS PARENT_DEP_ID,
       DEP_ID                           AS MASTER_PARENT_DEP_ID
FROM   DEPARTMENT
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR PARENT_DEP_ID = DEP_ID

Results :

|   DEP_ID | DEP_NAME | PARENT_DEP_ID | MASTER_PARENT_DEP_ID |
|----------|----------|---------------|----------------------|
| DEP_2000 |     Dep0 |        (null) |             DEP_2000 |
| DEP_2400 |     Dep1 |      DEP_2000 |             DEP_2000 |
| DEP_2410 |     Dep2 |      DEP_2400 |             DEP_2000 |
| DEP_2420 |     Dep3 |      DEP_2400 |             DEP_2000 |

注意:通过将树从每个元素遍历到根,而不是反向遍历,您不需要 START WITH 子句或单独的查询来查找根。

如果没有 DEP_2000 行(只需更改 DEP_ID 到 PARENT_DEP_ID ):

SQL Fiddle

Oracle 11g R2架构设置 :

CREATE TABLE DEPARTMENT( DEP_ID, DEP_NAME, PARENT_DEP_ID ) AS
--SELECT 'DEP_2000', 'Dep0', NULL       FROM DUAL UNION ALL
SELECT 'DEP_2400', 'Dep1', 'DEP_2000' FROM DUAL UNION ALL
SELECT 'DEP_2410', 'Dep2', 'DEP_2400' FROM DUAL UNION ALL
SELECT 'DEP_2420', 'Dep3', 'DEP_2400' FROM DUAL;

查询1 :

SELECT CONNECT_BY_ROOT( DEP_ID )        AS DEP_ID,
       CONNECT_BY_ROOT( DEP_NAME )      AS DEP_NAME,
       CONNECT_BY_ROOT( PARENT_DEP_ID ) AS PARENT_DEP_ID,
       PARENT_DEP_ID                    AS MASTER_PARENT_DEP_ID
FROM   DEPARTMENT
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR PARENT_DEP_ID = DEP_ID

Results :

|   DEP_ID | DEP_NAME | PARENT_DEP_ID | MASTER_PARENT_DEP_ID |
|----------|----------|---------------|----------------------|
| DEP_2400 |     Dep1 |      DEP_2000 |             DEP_2000 |
| DEP_2410 |     Dep2 |      DEP_2400 |             DEP_2000 |
| DEP_2420 |     Dep3 |      DEP_2400 |             DEP_2000 |