我如何记住CONNECT BY查询中应该围绕PRIOR采取哪种方式 [英] How do I remember which way round PRIOR should go in CONNECT BY queries
问题描述
我的记忆很糟.每当我在Oracle中执行CONNECT BY查询时-我的意思是每次-我必须认真思考,通常通过反复试验来确定PRIOR应该采用哪种参数.
I've a terrible memory. Whenever I do a CONNECT BY query in Oracle - and I do mean every time - I have to think hard and usually through trial and error work out on which argument the PRIOR should go.
我不知道为什么我不记得-但是我不知道.
I don't know why I don't remember - but I don't.
有人记忆方便吗,所以我一直记得吗?
Does anyone have a handy memory mnemonic so I always remember ?
例如:
要从节点上向下走一棵树-显然,我必须向上看:)-您可以执行以下操作:
To go down a tree from a node - obviously I had to look this up :) - you do something like:
select
*
from
node
connect by
prior node_id = parent_node_id
start with
node_id = 1
所以-我以node_id
为1(分支的顶部)开始,查询查找parent_node_id
= 1的所有节点,然后向下迭代到树的底部.
So - I start with a node_id
of 1 (the top of the branch) and the query looks for all nodes where the parent_node_id
= 1 and then iterates down to the bottom of the tree.
要先在父级上对树进行上:
To go up the tree the prior goes on the parent:
select
*
from
node
connect by
node_id = prior parent_node_id
start with
node_id = 10
因此,从分支的某个地方开始(在本例中为node_id = 10
),Oracle首先获取parent_node_id
与node_id
为10的节点相同的所有节点.
So starting somewhere down a branch (node_id = 10
in this case) Oracle first gets all nodes where the parent_node_id
is the same as the one for which node_id
is 10.
编辑:我仍然弄错了,所以我想添加一个澄清的编辑以扩展接受的答案-这就是我现在的记忆方式:
EDIT: I still get this wrong so thought I'd add a clarifying edit to expand on the accepted answer - here's how I remember it now:
select
*
from
node
connect by
prior node_id = parent_node_id
start with
node_id = 1
此SQL的英语"版本我现在读为...
The 'english language' version of this SQL I now read as...
在NODE中,从 其中
node_id = 1
,下一行 选定的有其parent_node_id
等于前一个node_id
(先前)行.
In NODE, starting with the row in which
node_id = 1
, the next row selected has itsparent_node_id
equal tonode_id
from the previous (prior) row.
编辑:Quassnoi很有意思-编写SQL的顺序使事情变得容易得多.
EDIT: Quassnoi makes a great point - the order you write the SQL makes things a lot easier.
select
*
from
node
start with
node_id = 1
connect by
parent_node_id = prior node_id
这让我感觉很清楚-开始于"给出了选择的第一行,连接依据"给出了下一行-在这种情况下,node_id = 1的子代.
This feels a lot clearer to me - the "start with" gives the first row selected and the "connect by" gives the next row(s) - in this case the children of node_id = 1.
推荐答案
考虑选择记录的顺序:每条记录的链接后列必须与PRIOR记录上的链接前列相匹配选择.
Think about the order in which the records are going to be selected: the link-back column on each record must match the link-forward column on the PRIOR record selected.
这篇关于我如何记住CONNECT BY查询中应该围绕PRIOR采取哪种方式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!