CONNECT BY 用于具有两个 JOINS 的两个表

Question

我有 3 个表：

• 其中两个具有分层结构
  （如递归类型层次结构的“维度”）；
• 一种具有求和数据的方法（例如带有 X 列的“事实”）。

它们在这里：

1. DIM1 (ID1, PARENT2, NAME1)
2. DIM2 (ID2, PARENT2, NAME2)
3. FACTS (ID1, ID2, X)

DIM1 表示例：

--        1   0  DIM1  
----      2   1  DIM1-A  
------    3   2  DIM1-A-A  
--------  4   3  DIM1-A-A-A  
--------  5   3  DIM1-A-A-B  
------    6   2  DIM1-A-B  
--------  7   6  DIM1-A-B-A  
--------  8   6  DIM1-A-B-B  
------    9   2  DIM1-A-C  
----     10   1  DIM1-B  
------   11  10  DIM1-B-C  
------   12  10  DIM1-B-D  
----     13   1  DIM1-C  

DIM2 表示例：

--        1   0  DIM2  
----      2   1  DIM2-A  
------    3   2  DIM2-A-A  
--------  4   3  DIM2-A-A-A  
--------  5   3  DIM2-A-A-B  
--------  6   3  DIM2-A-B-C  
------    7   2  DIM2-A-B  
----      8   1  DIM2-B  
----      9   1  DIM2-C  

FACTS 表示例：

 1   1  100  
 1   2   30  
 1   3  500  
 --    ................  
 13  9  200  

我想创建 < strong>唯一 SELECT，我将在其中指定 DIM1 的父级（例如 DIM1-A 的 ID1=2）和 DIM2 的父级（例如 < code>ID2=2 for DIM2-A) 和 SELECT 将生成如下报告：

Name_of_1 Name_of_2 Sum_of_X  
--------- --------- ----------  
DIM1-A-A  DIM2-A-A  (some sum)  
DIM1-A-A  DIM2-A-B  (some sum)  
DIM1-A-B  DIM2-A-A  (some sum)  
DIM1-A-B  DIM2-A-B  (some sum)  
DIM1-A-C  DIM2-A-A  (some sum)  
DIM1-A-C  DIM2-A-B  (some sum)  

我想使用 CONNECT BY 短语，START WITH 短语、SUM 短语、GROUP BY 短语以及 OUTER 或 INNER (?) <代码>加入。我不需要 Oracle 10.2 的其他扩展。

换句话说：仅使用“经典”SQL 和
仅适用于层次结构查询的 Oracle 扩展。

是否可以？

我尝试了一些带有问题的实验
与 Oracle 混合使用 Connect by、内连接和求和< /a>

（这里有一个非常很好的解决方案，但仅适用于一个
维度表（“任务”），但我需要JOIN 两个维度表到一个事实表），但我没有成功。

Answer 1

“Some sum”不是很具有描述性，所以我根本不明白为什么你需要 CONNECT BY 。

SELECT  dim1.name, dim2.name, x
FROM    (
        SELECT  id1, id2, SUM(x) AS x
        FROM    facts
        GROUP BY
                id1, id2
        ) f
JOIN    dim1
ON      dim1.id = f.id1
JOIN    dim2
ON      dim2.id = f.id2

Answer 2

我认为您想要做的是获取事实表中按最上面的子项分组的指定行的所有子项的值的总和。这意味着在上面的示例中，第一行的结果将是 (DIM1-AA、DIM1-AAA、DIM1-AAB) 和 (DIM2-AA、DIM2-AAA、DIM2-AAB、DIM3 -AAC) 在 FACTS 表中找到。有了这个假设，我得出了以下解决方案：（

SELECT root_name1, root_name2, SUM(X)
FROM ( SELECT CONNECT_BY_ROOT(name1) AS root_name,
             id1
         FROM dim1
      CONNECT BY parent1 = PRIOR id1
      START WITH parent1 = 2) d1
     CROSS JOIN
     ( SELECT CONNECT_BY_ROOT(name2) AS root_name,
              id2
         FROM dim2
      CONNECT BY parent2 = PRIOR id2
      START WITH parent2 = 2) d2
     LEFT OUTER JOIN
     facts
     ON     d1.id1 = facts.id1
        AND d2.id2 = facts.id2
GROUP BY root_name1, root_name2

这也假设 FACTS 的列被命名为 ID1、ID2 和 X。）