分层查询CONNECT BY Oracle [英] Hierarchical Query CONNECT BY Oracle

问题描述

我正在尝试在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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！