如果Oracle分层表中的子数据为空,则获取父数据
在Oracle 10g中,我有以下分层表:
corp_id, parent_corp_id, col1, col2, col3
我想将结构展平,以便我们得到第一行的数据,其中col1 OR col2 OR col3不为null。
举个例子,假设我有:
corp_id = 1
parent_corp_id = null
col1 = 5
col2 = NULL
col3 = NULL
corp_id = 3
parent_corp_id = 1
col1 = NULL
col2 = NULL
col3 = NULL
这个查询的结果会给我:
corp_id, parent_corp_id, col1, col2, col3
3 , 1 , 5 , NULL, NULL
另一种情况:假设我把col2 = 6放在corp_id = 3的地方
那么,结果集应该是:
corp_id, parent_corp_id, col1, col2, col3
3 , 1 , NULL, 6, NULL
换句话说,如果孩子在这三列中有一列有数据,我们就抓住它。 否则,我们尝试父母等,等等。 不应该超过3个级别,但可以有3个级别进行研究。
对分层查询来说很新,所以如果这是一个基本问题,请原谅我。
使用coalesce()
函数,它返回列表中的第一个非空值:
select
c.corp_id,
c.parent_corp_id,
coalesce(c.col1, p.col1) col1,
coalesce(c.col2, p.col2) col2,
coalesce(c.col3, p.col3) col3
from mytable c
left join mytable p on p.corp_id = c.parent_corp_id
要获得“具有非空值的第一行”,请添加:
where coalesce(c.col1, p.col1, c.col2, p.col2, c.col3, p.col3) is not null
and rownum = 1
你确实需要使用hiearchial查询(w / connect by子句),因为你有一个有孩子的父母,而且这个孩子是另一个孩子的父母(尽管你的例子数据并没有使这个情况发挥作用)然而,要求您显示'first not null col1'和'first not null col2'等是与分层关系完全不同的问题。
尝试以下操作,我将一些额外的示例数据添加到小提琴中(请查看左侧的DDL)以便说明。
它看起来像你期望的输出中你不想显示最高级别的父母,这就是为什么我在末尾放置“where s.parent_corp_id不为空”的原因。 如果你确实想要展示这些,请把这条线路拿出来。
否则,这将显示基于其组的col1 / col2 / col3值。 请注意,示例2中的父级如何为高级父节点,其中4作为子节点,4也是8的父节点。因此,corp_id 8和4是同一分支的一部分,因此它们显示相同的col1 / col2 / col3值,根据您的要求,这些都是整个分支的第一个非空值。
小提琴: http ://sqlfiddle.com/#!4/ef218/14/0
with sub as
(select corp_id,
parent_corp_id,
col1,
col2,
col3,
level as lvl,
rownum - level as grp
from tbl
connect by prior corp_id = parent_corp_id
start with parent_corp_id is null),
col1_lvl as
(select grp, col1
from sub s
where s.lvl = (select min(x.lvl)
from sub x
where x.col1 is not null
and x.grp = s.grp)),
col2_lvl as
(select grp, col2
from sub s
where s.lvl = (select min(x.lvl)
from sub x
where x.col2 is not null
and x.grp = s.grp)),
col3_lvl as
(select grp, col3
from sub s
where s.lvl = (select min(x.lvl)
from sub x
where x.col3 is not null
and x.grp = s.grp))
select s.corp_id, s.parent_corp_id, c1.col1, c2.col2, c3.col3
from sub s
left join col1_lvl c1
on s.grp = c1.grp
left join col2_lvl c2
on s.grp = c2.grp
left join col3_lvl c3
on s.grp = c3.grp
where s.parent_corp_id is not null
如果这没有提供您期望的输出结果,那么请根据我使用的样本数据提供我在DDL中使用的数据的预期输出。
链接地址: http://www.djcxy.com/p/93915.html上一篇: Getting parent data if child data is null in Oracle hierarchical table
下一篇: Insert Update trigger how to determine if insert or update