我正在尝试在oracle中实现Connect By查询.试图了解它是如何工作的.所以我有一张简单的桌子 看起来像:
I am trying to implement the Connect By query in oracle. Trying to understand how it works. So I have a simple table which looks likes:
empno ename mno 1 KS null 2 AB 2 3 BC 1 4 TR 3 5 QE 2 6 PL 3 7 LK 6查询
SELECT empno, ename, mno FROM test START WITH ename = 'LK' CONNECT BY PRIOR empno = mno;因此,当名称为LK时,我应该获得以下父行/子行LK> PL> BC> KS. SQLFIDDLE 但我没有得到正确的结果.我做错了什么?
So when the name is LK I should get the following parent/child rows LK>PL>BC>KS. The SQLFIDDLE But I am not getting the correct results. What I am doing wrong ?
推荐答案不,您不应该.
LK的父母是PL. PL的父母是BC. BC的父母是KS. KS没有父母.当您尝试从LK启动树时,会得到所有的子级-没有,因为没有记录的mno = 7.
LK's parent is PL. PL's parent is BC. BC's parent is KS. KS has no parent. When you're trying to start tree from LK, you get all it's children - none, because there are no records with mno = 7.
您在查询中混淆了父子顺序.如果您希望从叶到根解开这三个,则应该询问
You muddled parent-child order in your query. If you wish to unwind the three from leaf to root, you should ask
SELECT empno, ename, mno FROM test START WITH ename = 'LK' CONNECT BY empno = PRIOR mno;如果您希望看到树成自然秩序,您应该询问
If you wish to see the tree into the natural order, you should to ask
SELECT empno, ename, mno FROM test START WITH mno is null CONNECT BY PRIOR empno = mno;更多推荐
分层查询CONNECT BY Oracle
发布评论