Oracle - 如何在层次查询中使用连接并避免笛卡尔积

Question

我有一个需求，需要从两个表中选择数据 - 第一个表包含层次结构数据（STR 表），另一个表是一个层次结构的起始点（REQ 表），我需要选择层次结构并将其与 REQ 表的另一列组合起来。

以下是一个示例：

CREATE TABLE REQ (prop int,          ord  varchar2(10));
CREATE TABLE STR (par  varchar2(10), chld varchar2(10));

INSERT INTO REQ VALUES (100,'A');
INSERT INTO REQ VALUES (101,'A');
INSERT INTO REQ VALUES (102,'B');
INSERT INTO STR VALUES ('A','A1');
INSERT INTO STR VALUES ('A','A2');
INSERT INTO STR VALUES ('A1','A3');
INSERT INTO STR VALUES ('A2','A5');
INSERT INTO STR VALUES ('A3','A6');
INSERT INTO STR VALUES ('B','B1');
INSERT INTO STR VALUES ('B','B2');

从STR表中进行基本查询，例如得到以下层次结构：

SELECT par, chld, level
FROM STR
CONNECT BY PRIOR chld = par
START WITH PAR IN (SELECT ord FROM REQ WHERE prop = 100);

层级关系没问题：

PAR        CHLD            LEVEL
---------- ---------- ----------
A          A1                  1
A1         A3                  2
A3         A6                  3
A          A2                  1
A2         A5                  2

我需要将REQ表中prop列的值添加到结果中。 我期望得到以下结果:

PAR        CHLD            LEVEL  PROP 
---------- ---------- ---------- ----- 
A          A1                  1   100   
A1         A3                  2   100
A3         A6                  3   100
A          A2                  1   100
A2         A5                  2   100

当我尝试在JOIN和CONNECT BY中同时合并两个表时，会得到类似笛卡尔积的结果，一些行会重复出现：

SELECT STR.par, STR.chld, level, REQ.prop, REQ.ord
FROM STR
,    REQ
WHERE REQ.prop = 100
CONNECT BY PRIOR STR.chld = STR.par
START WITH STR.PAR = REQ.ord;

结果与我期望的不同：

PAR        CHLD            LEVEL       PROP ORD      
---------- ---------- ---------- ---------- ----------
A          A1                  1        100 A         
A1         A3                  2        100 A         
A3         A6                  3        100 A         
A3         A6                  3        100 A        ! extra
A3         A6                  3        100 A        ! rows !
A1         A3                  2        100 A        !
A3         A6                  3        100 A        ! 
A3         A6                  3        100 A        ! 
A3         A6                  3        100 A        ! 
A          A2                  1        100 A         
A2         A5                  2        100 A         
A2         A5                  2        100 A        ! 
12 rows selected 

有没有什么办法可以更正查询以获取期望的数据？

- LiborStefek

Answer 1

以下查询应该可以解决问题——不知道是否理想，但是它可以工作：

SELECT DISTINCT STR.par, STR.chld, level
     , connect_by_root req.prop AS prop
     , connect_by_root req.ord AS ord
  FROM str
  LEFT JOIN REQ
    ON REQ.ord = str.par
CONNECT BY PRIOR STR.chld = STR.par
  START WITH REQ.prop = 100

- Radagast81

---

但是你的查询更容易理解，在更复杂的情况下看起来更好，至少对我来说是这样。感谢您发布它。 - LiborStefek

Answer 2

我找到了另一种方法 - 不知道是否理想，但似乎可以工作：

SELECT STR.par, STR.chld, level, REQ.prop, REQ.ord
FROM STR
,    REQ
CONNECT BY PRIOR STR.chld = STR.par
AND        PRIOR REQ.prop = REQ.prop
START WITH STR.PAR = REQ.ord AND REQ.prop = 100;

- LiborStefek