在每个级别的层次结构查询总和
问题描述:
我有以下表结构。我想从TAB2的每个级别获得总和。在每个级别的层次结构查询总和
TAB1在级别列中存储层次结构。
TAB1
----- ----- ---- ----
KEY L1 L2 L3
---- ----- ----- ----
A A
B A B
C A B C
D A B D
TAB2
-----
KEY TC
---- ----
A 10
B 11
C 6
D 12
X 11
Expected Output:
KEY SUM
---- ----
A 39
B 29
C 6
D 12
X 11
这里是SQLFiddle链接:LINK TO FIDDLE
答
甲骨文设置
Create table TAB1 (pKey varchar2(10),level1 varchar2(10),level2 varchar2(10),level3 varchar2(10),level4 varchar2(10));
insert into TAB1(pKey,level1) values('A','A');
insert into TAB1(pKey,level1,level2) values('B','A','B');
insert into TAB1(pKey,level1,level2,level3) values('C','A','B','C');
insert into TAB1(pKey,level1,level2,level3) values('D','A','B','D');
Create table TAB2 (pKey varchar(10), tc integer);
insert into TAB2(pKey,tc) values('A',10);
insert into TAB2(pKey,tc) values('B',11);
insert into TAB2(pKey,tc) values('C',6);
insert into TAB2(pKey,tc) values('D',12);
insert into TAB2(pKey,tc) values('X',11);
查询:
SELECT t2.pKey,
SUM(COALESCE(t4.TC, t2.tc)) AS tc
FROM tab2 t2
LEFT OUTER JOIN
tab1 t1
ON (t2.pKey = t1.pKey)
LEFT OUTER JOIN
tab1 t3
ON ( t1.level1 = t3.level1
AND (t1.level2 IS NULL OR t1.level2 = t3.level2)
AND (t1.level3 IS NULL OR t1.level3 = t3.level3)
AND (t1.level4 IS NULL OR t1.level4 = t3.level4))
LEFT OUTER JOIN
tab2 t4
ON (t3.pKey = t4.pKey)
GROUP BY t2.pKey;
输出:
PKEY TC
---------- ----------
D 12
A 39
B 29
C 6
X 11
答
在下面提供的解决方案(包括输入数据作为因式分解的子查询),第一我展示如何使用unpivot
和附加操作正常化tab1
(结果是因子分解子查询n
为“n
ormalized” )。然后,如果你有正常形式的数据,则可以通过直接应用我的代码底部显示的标准分层查询来获得输出结果。
with
tab1 (key, L1, L2, L3) as (
select 'A', 'A', null, null from dual union all
select 'B', 'A', 'B' , null from dual union all
select 'C', 'A', 'B' , 'C' from dual union all
select 'D', 'A', 'B' , 'D' from dual
),
tab2 (key, TC) as (
select 'A', 10 from dual union all
select 'B', 11 from dual union all
select 'C', 6 from dual union all
select 'D', 12 from dual union all
select 'X', 11 from dual
),
unpiv (key, l, ancestor) as (
select key, to_number(substr(lv, 2)), ancestor from tab1
unpivot (ancestor for lv in (L1, L2, L3))
),
d (key, depth) as (
select key, max(l)
from unpiv
group by key
),
n (child, parent, TC) as (
select d.key, u.ancestor, tab2.TC
from unpiv u
right outer join d
on u.key = d.key and u.l = d.depth - 1
left outer join tab2
on d.key = tab2.key
)
SELECT key, sum(TC) as sum_TC
from (
select connect_by_root child as key, TC
from n
connect by prior child = parent
)
group by key
order by key;
一路上,在unpiv
,我已经把所有的父子关系,所以我可以直接加入与tab2
上unpiv.key = tab2.key
和ancestor
(类似于MT0的解决方案)总结TC
分组。相反,我想演示两个单独的步骤:(1)规范化tab1
和(2)在规范化表上使用分层查询是多么容易。
输出:
KEY SUM_TC
--- ----------
A 39
B 29
C 6
D 12
这里是一个伟大的地方开始。 http://spaghettidba.com/2015/04/24/how-to-post-at-sql-question-on-a-public-forum/ –
请为此数据提供所需的输出,因为它不清楚什么你的意思是*每个级别的总和*。我想至少有3种解释。 – trincot
添加到trincot的评论 - 不要只提供所需的输出,用英文解释(无代码!)如何输出。 – mathguy