时间戳

首页 > 解决方案 > 在 sql oracle 中获取父级的 silbilng

sql - 在 sql oracle 中获取父级的 silbilng

问题描述

鉴于父子关系。我想获得所有父母和给定 childId 的 silbling

parentId |  chilId 

--------------------
null     |   Node0

Node0    |   Node1

Node0    |   Node2

Node1    |   Node3

Node1    |   Node4

Node2    |   Node5

Node3    |   Node6

Node3    |   Node7

null     |    C0

C0       |    C1

C0       |    C3

C3       |    C1

---------------------

假设如果选择 childID 作为 Node6 我应该得到以下结果：

null     |   Node0

Node0    |   Node1

Node0    |   Node2

Node1    |   Node3

Node1    |   Node4

Node2    |   Node5

Node3    |   Node6

Node3    |   Node7

标签： sqloracleparent-child

解决方案

我认为您可以使用connect by prior以下功能实现它：

Node6作为输入参数传递，WHERE在两个地方的子句中使用

SQL> WITH DATA (PARENT, CHILD) AS
  2  (SELECT null , 'Node0'   FROM DUAL UNION ALL
  3  SELECT 'Node0', 'Node1' FROM DUAL UNION ALL
  4  SELECT 'Node0', 'Node2' FROM DUAL UNION ALL
  5  SELECT 'Node1', 'Node3' FROM DUAL UNION ALL
  6  SELECT 'Node1', 'Node4' FROM DUAL UNION ALL
  7  SELECT 'Node2', 'Node5' FROM DUAL UNION ALL
  8  SELECT 'Node3', 'Node6' FROM DUAL UNION ALL
  9  SELECT 'Node3', 'Node7' FROM DUAL UNION ALL
 10  SELECT null , 'C0' FROM DUAL UNION ALL
 11  SELECT 'C0', 'C1'  FROM DUAL UNION ALL
 12  SELECT 'C0', 'C3'  FROM DUAL UNION ALL
 13  SELECT 'C3', 'C1'  FROM DUAL),
 14  DATA_ACTUAL AS
 15  (SELECT PARENT,
 16          CHILD,
 17          CONNECT_BY_ROOT CHILD ROOT_,
 18          SYS_CONNECT_BY_PATH(PARENT, '/') CP FROM DATA
 19   CONNECT BY PRIOR CHILD = PARENT START WITH PARENT IS NULL)
 20  --
 21  SELECT PARENT,
 22         CHILD
 23    FROM DATA_ACTUAL
 24   WHERE ROOT_ = (SELECT ROOT_ FROM DATA_ACTUAL WHERE CHILD = 'Node6')
 25  AND CP NOT LIKE (SELECT '%' || PARENT || '/%' FROM DATA_ACTUAL WHERE CHILD = 'Node6');

PAREN CHILD
----- -----
      Node0
Node0 Node1
Node1 Node3
Node3 Node6
Node3 Node7
Node1 Node4
Node0 Node2
Node2 Node5

8 rows selected.

SQL>

将输入参数传递为Node2：

SQL> WITH DATA (PARENT, CHILD) AS
  2  (SELECT null , 'Node0'   FROM DUAL UNION ALL
  3  SELECT 'Node0', 'Node1' FROM DUAL UNION ALL
  4  SELECT 'Node0', 'Node2' FROM DUAL UNION ALL
  5  SELECT 'Node1', 'Node3' FROM DUAL UNION ALL
  6  SELECT 'Node1', 'Node4' FROM DUAL UNION ALL
  7  SELECT 'Node2', 'Node5' FROM DUAL UNION ALL
  8  SELECT 'Node3', 'Node6' FROM DUAL UNION ALL
  9  SELECT 'Node3', 'Node7' FROM DUAL UNION ALL
 10  SELECT null , 'C0' FROM DUAL UNION ALL
 11  SELECT 'C0', 'C1'  FROM DUAL UNION ALL
 12  SELECT 'C0', 'C3'  FROM DUAL UNION ALL
 13  SELECT 'C3', 'C1'  FROM DUAL),
 14  DATA_ACTUAL AS
 15  (SELECT PARENT,
 16          CHILD,
 17          CONNECT_BY_ROOT CHILD ROOT_,
 18          SYS_CONNECT_BY_PATH(PARENT, '/') CP FROM DATA
 19   CONNECT BY PRIOR CHILD = PARENT START WITH PARENT IS NULL)
 20  --
 21  SELECT PARENT,
 22         CHILD
 23    FROM DATA_ACTUAL
 24   WHERE ROOT_ = (SELECT ROOT_ FROM DATA_ACTUAL WHERE CHILD = 'Node2')
 25  AND CP NOT LIKE (SELECT '%' || PARENT || '/%' FROM DATA_ACTUAL WHERE CHILD = 'Node2');

PAREN CHILD
----- -----
      Node0
Node0 Node1
Node0 Node2

SQL>

干杯！！

推荐阅读