分层查询CONNECT BY Oracle

本文介绍了分层查询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;