oracle CONNECT BY 用于具有两个 JOINS 的两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3687712/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CONNECT BY for two tables with two JOINS
提问by mirek
I have 3 tables:
我有3张桌子：
- two with hierarchical structures
(like "dimensions" of recursive type of hierarchy); - one with summing data (like "facts" with X column).
- 两个具有层次结构
（如层次结构的递归类型的“维度”）； - 一个汇总数据（如带有 X 列的“事实”）。
They are here:
他们在这里：
- DIM1 (ID1, PARENT2, NAME1)
- DIM2 (ID2, PARENT2, NAME2)
- FACTS (ID1, ID2, X)
- DIM1（ID1、PARENT2、NAME1）
- DIM2（ID2、PARENT2、NAME2）
- 事实（ID1、ID2、X）
Example of DIM1 table:
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
Example of DIM2 table:
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
Example of FACTS table:
FACTS 表示例：
1 1 100
1 2 30
1 3 500
-- ................
13 9 200
And I would like to create the onlySELECTwhere I will specify the parent for DIM1 (for example ID1=2for DIM1-A) and parent for DIM2 (for example ID2=2for DIM2-A) and SELECTwill generate a report like this:
我想创建唯一SELECT一个我将为ID1=2DIM1（例如ID2=2DIM1-A）和DIM2（例如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)
I would like to use CONNECT BYphrase, START WITHphrase, SUMphrase, GROUP BYphrase, and OUTERor INNER(?) JOIN. I need no other extensions of Oracle 10.2.
我想使用CONNECT BY短语、START WITH短语、SUM短语、GROUP BY短语和OUTER或INNER(?) JOIN。我不需要 Oracle 10.2 的其他扩展。
In other words: only with "classic" SQL and
only Oracle extensions for hierarchy queries.
换句话说：仅适用于“经典”SQL 且
仅适用于层次结构查询的 Oracle 扩展。
Is it possible?
是否可以？
I tried some experiments with question in
Mixing together Connect by, inner join and sum with Oracle
我在Mixing together Connect by,inner join and sum with Oracle 中尝试了一些问题实验
(where is a very nice solutionbut only for one
dimension table ("Tasks"), but I need to JOINtwodimension tables to one facts table), but I was not successful.
（哪里是一个非常好的解决方案，但仅适用于一个
维度表（“任务”），但我需要将JOIN两个维度表转换为一个事实表），但我没有成功。
回答by Quassnoi
"Some sum" is not very descriptive, so I don't see why do you need CONNECT BYat all.
“一些总和”不是很具有描述性，所以我不明白你为什么需要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
回答by Allan
I think what you're trying to do is get the sum of the value in the facts table for all of the children of the specified rows grouped by the topmost children. This would mean that in your example above, the results for the first row would be the sum any intersections of (DIM1-A-A, DIM1-A-A-A, DIM1-A-A-B) and (DIM2-A-A, DIM2-A-A-A, DIM2-A-A-B, DIM3-A-A-C) found in the FACTS table. With that assumption, I have come to the following solution:
我认为您想要做的是获取由最上面的孩子分组的指定行的所有孩子的事实表中的值的总和。这意味着在上面的示例中，第一行的结果将是 (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
(This also assumes that the columns of FACTS are named ID1, ID2, and X.)
（这也假设 FACTS 的列被命名为 ID1、ID2 和 X。）
