SQL查询，以树状结构返回与其祖先匹配的行

Question

我有一个Oracle 12c数据库，其中有一个树状结构的表，用于组织单位（部门等）：

CREATE TABLE "OUS" (
  "ID" NUMBER(38,0) NOT NULL ENABLE, 
  "NAME" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
  "PARENT_ID" NUMBER(38,0),
  PRIMARY KEY("ID"),
  CONSTRAINT "OUS_HIERARCHY_FK" FOREIGN KEY ("PARENT_ID") REFERENCES "OUS" ("ID") ON DELETE CASCADE
);

因此，拥有这样的结构

| id | name          | parent_id |
| -: | ------------- | --------: |
|  1 | Root          |    (NULL) |
|  2 | Territorial 1 |         1 |
|  3 | Regional 1-1  |         2 |
|  4 | Alpha dept    |         3 |
|  5 | Beta dept     |         3 |
|  6 | Regional 1-2  |         2 |
|  7 | Gamma dept    |         6 |
|  8 | Delta dept    |         7 |
|  9 | Territorial 2 |         1 |
| 10 | Regional 2-1  |         9 |
| 11 | Epsilon dept  |        10 |
| 12 | Zeta dept     |        10 |

你可以用SQL创建它，比如：

INSERT INTO ous (id, name, parent_id) VALUES ( 13, 'Root',          NULL);
INSERT INTO ous (id, name, parent_id) VALUES (  2, 'Territorial 1',   13);
INSERT INTO ous (id, name, parent_id) VALUES (  1, 'Regional 1-1',     2);
INSERT INTO ous (id, name, parent_id) VALUES (  5, 'Alpha dept',       1);
INSERT INTO ous (id, name, parent_id) VALUES (  4, 'Beta dept',        1);
INSERT INTO ous (id, name, parent_id) VALUES (  6, 'Regional 1-2',     2);
INSERT INTO ous (id, name, parent_id) VALUES (  7, 'Gamma dept',       6);
INSERT INTO ous (id, name, parent_id) VALUES (  8, 'Delta dept',       6);
INSERT INTO ous (id, name, parent_id) VALUES (  9, 'Territorial 2',   13);
INSERT INTO ous (id, name, parent_id) VALUES (  3, 'Regional 2-1',     9);
INSERT INTO ous (id, name, parent_id) VALUES ( 15, 'Epsilon dept',     3);
INSERT INTO ous (id, name, parent_id) VALUES ( 12, 'Zeta dept',        3);

我需要找到一些OU，匹配给定的条件（如name = 'Alpha' OR name = 'Epsilon），并获得这些OU及其祖先的子树

例如：

| id | name          | parent_id |
| -: | ------------- | --------: |
|  1 | Root          |    (NULL) | ← Ancestor of Alpha and Epsilon
|  2 | Territorial 1 |         1 | ← Ancestor of Alpha
|  3 | Regional 1-1  |         2 | ← Ancestor of Alpha
|  4 | Alpha dept    |         3 | ← Matches the WHERE clause!
|  9 | Territorial 2 |         1 | ← Ancestor of Epsilon
| 10 | Regional 2-1  |         9 | ← Ancestor of Epsilon
| 11 | Epsilon dept  |        10 | ← Matches the WHERE clause!

我研究了SQL中的各种层次结构和递归查询：Oracle层次结构查询和CTE，但找不出一个可以返回这样结果的查询。

我使用的是Oracle数据库12c。

我尝试过以下查询：

SELECT ous.* FROM ous
WHERE name = 'Alpha' OR name = 'Epsilon'
START WITH 
  parent_id IS NULL
CONNECT BY
  PRIOR id = parent_id 
ORDER SIBLINGS BY name;

但当WHERE应用于所有行时，它返回0行（因此祖先被过滤）

我也试过：

WITH RECURSIVE all_nodes (id, parent_id, name) AS (
  SELECT ous.id, ous.parent_id, name FROM ous WHERE (name = 'Alpha' OR name = 'Epsilon')
  UNION
  SELECT ous.id, ous.parent_id, name FROM ous INNER JOIN all_nodes ON ous.parent_id = all_nodes.id
)
SELECT * FROM all_nodes INNER JOIN ous ON all_nodes.id = ous.id ORDER BY name;

但它返回错误SQL Error [905] [42000]: ORA-00905: keyword is missing

Answer 1

您可以使用递归CTE:来完成此操作

with t(name, id, parent_id) as (
      select name, id, parent_id
      from ous
      where name in ('alpha', 'epsilon')
      union all
      select ous.name, ous.id, ous.parent_id
      from t join
           ous
           on ous.id = t.parent_id
    )
select distinct t.id, t.name, t.parent
from t
order by t.id;

select distinct可能不是必需的。

递归CTE具有作为标准SQL的优势，因此该逻辑受到许多不同数据库的支持。

Answer 2

当然，您可以为此使用分层查询。问题是，如果您从多个叶开始，在某个时刻您将开始获得重复的行。您可以用"distinct"删除重复项，但这会影响性能，尤其是在非常大的表上，或者如果开始时有太多的叶子。归根结底，递归查询通常更难编写，但比层次查询更高效。

为了完整起见，这里是分层解决方案。使用SCOTT模式中的EMP表进行说明。首先显示直接的分层查询（以及输出中的重复项），然后显示带有"distinct"的版本

select empno, mgr
from   scott.emp
start with empno in (7902, 7788)
connect by prior mgr = empno
;
     EMPNO        MGR
---------- ----------
      7788       7566
      7566       7839
      7839
      7902       7566
      7566       7839
      7839

select distinct empno, mgr
from   scott.emp
start with empno in (7902, 7788)
connect by prior mgr = empno
;
     EMPNO        MGR
---------- ----------
      7839
      7566       7839
      7902       7566
      7788       7566