Oracle SQL递归查找非空列值的第一个实例 [英] Oracle SQL recursion to find first instance of non-null column value
问题描述
我将尝试解释桌子的布置方式，以便使我所需的内容更加清楚.
I'll try and explain how the table is laid out so that what I need might be a bit more clear.
###############################################################
# cid # iid # child cid # child iid # target cid # target iid #
###############################################################
# 112 # 1 # null # null # 116 # 1 #
# 112 # 2 # 112 # 1 # null # null #
# 112 # 3 # 112 # 1 # 116 # 2 #
# 112 # 4 # 112 # 1 # 100 # 3 #
# 112 # 101 # null # null # 116 # 101 #
# 112 # 102 # 112 # 101 # null # null #
# 112 # 103 # 112 # 101 # 116 # 102 #
# 112 # 201 # null # null # 116 # 201 #
# 112 # 202 # 112 # 201 # null # null #
# 112 # 203 # 112 # 201 # 116 # 202 #
# 112 # 301 # null # null # 116 # 301 #
# 112 # 302 # 112 # 301 # null # null #
# 112 # 302 # 112 # 301 # 116 # 302 #
上面是我要从中获取数据的表的简化表示.很抱歉，如果版式有点废话.这里的每一行都是一个对象.这些对象中的每个对象都可以具有子对象，因此，例如，第一行没有子对象，但链接到目标对象.第二行有一个子对象，并且没有链接到目标对象，但是，它通过具有目标对象的子cid和iid链接回了第一行.第三行也链接到第一行，但它也有一个目标对象，因此我实际上不希望回到第一行.
Above there is a cut down representation of the table I'm trying to get data from. Sorry if the layout is a bit crap. Each row here is an object. Each of these objects can have child objects so for example, the first row has no child objects but is linked to the target object. Row two has a child object and isn't linked to a target object, however, it is linked back to row 1 via the child cid and iid which does have a target object. Row three is also linked to row one but it also has a target object so I don't actually want to go back to row one.
其他表格
#########################################
# cid # iid # col1 # col2 # col3 # col4 #
#########################################
# 116 # 1 # a # null # 16 # 1 #
# 116 # 2 # b # 1 # 6 # null #
# 116 # 3 # n # 1 # 11 # 2 #
# 116 # 101 # n # 2 # 61 # 3 #
# 116 # 102 # b # null # 161 # 101 #
# 116 # 201 # a # 33 # 312 # 116 #
# 116 # 202 # a # 33 # 312 # 116 #
# 116 # 301 # s # 56 # 1321 # 33 #
# 116 # 302 # r # 6 # 22 # 12 #
结果表
###########################################################################################
# cid # iid # child cid # child iid # target cid # target iid # col1 # col2 # col3 # col4 #
###########################################################################################
# 112 # 1 # null # null # 116 # 1 # a # null # 16 # 1 #
# 112 # 2 # 112 # 1 # null # null # a # null # 16 # 1 #
# 112 # 3 # 112 # 1 # 116 # 2 # b # 1 # 6 # null #
# 112 # 4 # 112 # 1 # 100 # 3 # n # 1 # 11 # 2 #
# 112 # 101 # null # null # 116 # 101 # n # 2 # 61 # 3 #
# 112 # 102 # 112 # 101 # null # null # n # 2 # 61 # 3 #
# 112 # 103 # 112 # 101 # 116 # 102 # b # null # 161 # 101 #
# 112 # 201 # null # null # 116 # 201 # a # 33 # 312 # 116 #
# 112 # 202 # 112 # 201 # null # null # a # 33 # 312 # 116 #
# 112 # 203 # 112 # 201 # 116 # 202 # a # 33 # 312 # 116 #
# 112 # 301 # null # null # 116 # 301 # s # 56 # 1321 # 33 #
# 112 # 302 # 112 # 301 # null # null # s # 56 # 1321 # 33 #
# 112 # 302 # 112 # 301 # 116 # 302 # r # 6 # 22 # 12 #
[仅在第一个表中澄清目标cid和iid与链接到另一个表中的cid和iid有关.]
[Just to clarify, in the first table, target cid and iid relate to cid and iid in the other table im linking to it.]
基本上，我需要递归地遍历表，直到一行具有目标对象引用为止.
Essentially what I need is to recursively go back through the table until a row has a target object reference.
如果一行同时具有子c/i id和目标c/i id，我只需要目标c/i id.
If a row has both a child c/i id and a target c/i id i just want the target c/i id.
有人能指出我正确的方向吗?
Can anybody point me in the right direction?
我正在慢慢阅读 http://docs.oracle.com/cd/B19306_01/server .102/b14200/queries003.htm ，但我觉得有点混乱.我不太会成为更简单的SQL查询的专家，所以递归现在有点麻烦了.
I'm slowly reading through http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm but I'm finding it a bit confusing. I wouldn't exactly be an expert in the easier SQL queries so recursion is a bit over my head right now.
谢谢
添加了其他表格和结果的示例
Added example of other table and outcome
推荐答案
我不知道您到底需要什么，但是 你可以先从这句话开始
I dont know what exactly do you need, but you could start with tihs statment
select cid, iid, level, connect_by_root(target_cid), connect_by_root(target_iid)
from tab
connect by prior cid = child_cid
AND prior iid = child_iid
AND target_cid is null
;
然后过滤所需的条目
select *
from
(
select cid, iid, level, connect_by_root(target_cid) as target_cid, connect_by_root(target_iid) as target_iid
from tab
connect by prior cid = child_cid
AND prior iid = child_iid
AND target_cid is null
)
where target_cid is not null
;
CID IID TARGET_CID TARGET_IID
++++++++++++++++++++++++++++++
112 1 116 1
112 2 116 1
112 3 116 2
112 4 100 3
112 101 116 101
112 102 116 101
112 103 116 102
112 201 116 201
112 202 116 201
112 203 116 202
112 301 116 301
112 302 116 301
112 302 116 302
这篇关于Oracle SQL递归查找非空列值的第一个实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
