我如何记住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 its parent_node_id equal to node_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屋！