sql - 输出匹配条件的记录，以及它们的父记录直到根记录。甲骨文 SQL

Question

有这样一张Oracle表：

+----+-----+--------+
| ID | PID |  NAME  |
+----+-----+--------+
|  1 |     | testX  |
|  2 |     | test2  |
|  3 |   2 | test3  |
|  4 |   3 | testX  |
|  5 |   3 | test5  |
|  6 |   3 | test6  |
|  7 |   4 | test7  |
|  8 |   5 | test8  |
|  9 |   3 | test9  |
| 10 |   4 | test10 |
| 11 |   5 | testX  |
| 12 |   5 | test12 |
+----+-----+--------+

，其中 pid 是父记录的 id。

需要输出所有符合条件的记录，以及它们的父记录直到根记录。此类父记录不应与在搜索阶段找到的父记录重复。

例如，在这种情况下where name = 'testX'，应该得到这样的结果：

+----+-----+-------+
| ID | PID | NAME  |
+----+-----+-------+
|  1 |     | testX |
|  2 |     | test2 |
|  3 |   2 | test3 |
|  4 |   3 | testX |
|  5 |   3 | test5 |
| 11 |   5 | testX |
+----+-----+-------+

怎么做？

PS甲骨文11.2.0.4.0。

标签： sqloracleconnect-byhierarchical-query

Answer 1

我确信有一种更优雅的方法可以做到这一点，但这就是我想出的。

这是生成示例数据的 with 子句：

with testdata as
(select 1 ID,   null PID, 'testX' NAME from dual union all
select 2 ,      null,     'test2'      from dual union all
select 3 ,      2,        'test3'      from dual union all
select 4 ,      3,        'testX'      from dual union all
select 5 ,      3,        'test5'      from dual union all
select 6 ,      3,        'test6'      from dual union all
select 7 ,      4,        'test7'      from dual union all
select 8 ,      5,        'test8'      from dual union all
select 9 ,      3,        'test9'      from dual union all
select 10,      4,        'test10'     from dual union all
select 11,      5,        'testX'      from dual union all
select 12,      5,        'test12'     from dual)

这是查询：

select distinct id, pid, name
from(
select sys_connect_by_path(name,'/') path,
       id, pid, name
from testdata
connect by prior PID = ID)
where instr(path,'/testX') > 0
order by id

我用来SYS_CONNECT_BY_PATH从所有父母那里获取姓名字段。然后我只是使用 . 检查了这testX是字符串中的元素之一instr。

我的结果是：

ID      PID     NAME
1               testX
2               test2
3       2       test3
4       3       testX
5       3       test5
11      5       testX