Oracle SQL小示例系列-connect by prior

心已赠人 2023-06-27 12:39 7阅读 0赞

对于oracle中使用connect by…prior实现树查询两种方式：自顶而下和自低而上，总是出现混淆状态，经常通过脚本来将prior放在等号前后来查看效果来分清使用的是哪种方法，因此对理清connect by…prior的树查询进行整理。

认识connect by prior

**1.connect by prior基本语法**

select ... from <TableName>  
where <Conditional-1>  
start with <Conditional-2>  
connect by <Conditional-3>;  
  
<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。  
<Conditional-2>：查询结果重起始根结点的限定条件。  
<Conditional-3>：连接条件，其中用prior表示上一条记录，比如connect by prior id=praentid就是说上一条记录的id是本条记录的praentid，即本记录的父亲是上一条记录。

**2.connect by prior递归理解：（以自顶而下举例）**

**2.1.****level关键字理解**

**        **由于在分解connect by prior实现中需要借助level关键字，因此简单说明下level的含义：  
        在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定

脚本：

SELECT t.\*,level FROM EMP t CONNECT BY MGR= PRIOR EMPNO START WITH EMPNO=7839 order by level;

效果：

![format_png][]

图一

**2.2.****使用子查询语句来分解递归**

\--1.执行初始话子查询来生成查询子表

SELECT \* FROM EMP WHERE EMPNO=7839;

![format_png 1][]

与图1中的level=1的值一致。

\--2.通过第一步中的查询子表来执行递归子查询  
SELECT B.\*  
FROM (SELECT EMPNO,MGR FROM EMP WHERE EMPNO=7839) A,EMP B  
WHERE A.EMPNO=B.MGR;

![format_png 2][]

与图1中的level=2的值一致。

\--3.通过第二步中的查询子表来执行递归子查询  
SELECT B.\*  
FROM ( SELECT B.EMPNO,B.MGR  
       FROM (SELECT EMPNO,MGR FROM EMP WHERE EMPNO=7839) A,EMP B  
       WHERE A.EMPNO=B.MGR) A,EMP B  
WHERE A.EMPNO=B.MGR;

![format_png 3][]

与图1中的level=3的值一致。

\--4.通过第三步中的查询子表来执行递归子查询  
SELECT B.\*  
FROM (SELECT B.EMPNO,B.MGR  
      FROM (SELECT B.EMPNO,B.MGR  
            FROM (SELECT EMPNO,MGR FROM EMP WHERE EMPNO=7839) A,EMP B  
            WHERE A.EMPNO=B.MGR) A,EMP B  
      WHERE A.EMPNO=B.MGR) A,EMP B  
WHERE A.EMPNO=B.MGR;

![format_png 4][]

与图1中的level=4的值一致。

通过第四步查询子表中新添加的行，再次执行递归子查询。这次，该查询不生成结果。  
  
以上为个人理解学习分解，如有较好的方式描述，可互相学习。

示例演示

**      **  以scott用户下的emp表举例查询对应效果，emp表有个字段，一个是empno(员工编号)，另一个是mgr(上级经理编号)

SELECT \* FROM EMP;

![format_png 5][]

**1.向下递归遍历**

SELECT \* FROM EMP CONNECT BY MGR= PRIOR EMPNO START WITH EMPNO=7839;

![format_png 6][]

**2.向上递归遍历**

SELECT \* FROM EMP CONNECT BY PRIOR MGR=EMPNO START WITH EMPNO=7844;

![format_png 7][]

![format_png 8][]

![format_png 9][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cDovL21tYml6LnFwaWMuY24vbW1iaXpfcG5nL294QUhld2FMMnJpYXc5YWI2dHNBSk40REFpYVp0Y081WUhwWXA1bUJkaWFwZDBEVXFyNkpJRnd0MzJiOXN1dG1tRVZ3QTVYdTU5aWFEZ05wZEZ4STBHbFQ1Zy8w?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cDovL21tYml6LnFwaWMuY24vbW1iaXpfcG5nL294QUhld2FMMnJpYXc5YWI2dHNBSk40REFpYVp0Y081WUhoRENTSUhpYzdid040RXNBNG9valdsRkdpYldhMWtNMURlR0RmbmlhczNhSzBsaWJvVHI2eHg5aWNtUS8w?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cDovL21tYml6LnFwaWMuY24vbW1iaXpfcG5nL294QUhld2FMMnJpYXc5YWI2dHNBSk40REFpYVp0Y081WUhSbEZ2Rnk2UDUzR3N3dXM0dHNCejRPRXRpYUVPbHVGd1o1WjZwRUU5RnVJemlhekVqMEhyclROUS8w?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cDovL21tYml6LnFwaWMuY24vbW1iaXpfcG5nL294QUhld2FMMnJpYXc5YWI2dHNBSk40REFpYVp0Y081WUhBTE90SHNUN2c5YW1raGJWVDlRek1ZSkhsckwxMUtsc3ZoMGt2bWN2c1c2ODhKYzZYREFDM2cvMA?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cDovL21tYml6LnFwaWMuY24vbW1iaXpfcG5nL294QUhld2FMMnJpYXc5YWI2dHNBSk40REFpYVp0Y081WUhGdE1CUFdpYUtNV0MzclE3dndRb2ZMeElZMGljaWNtODhpY1dCbUlLb1BQd2ljVk1GZWNDZTZpYzdqdGcvMA?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cDovL21tYml6LnFwaWMuY24vbW1iaXpfcG5nL294QUhld2FMMnJpYXc5YWI2dHNBSk40REFpYVp0Y081WUg5cFE0bFptbXhNTEIwZTJwSmU3aWNzd3ZzbFdsUGpuNUJpYUhtOUpteGFZS2libVdSdzdLOGNuZ0EvMA?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cDovL21tYml6LnFwaWMuY24vbW1iaXpfcG5nL294QUhld2FMMnJpYXc5YWI2dHNBSk40REFpYVp0Y081WUhlTWliSHJLSzBBYmF3Y2FRc29GR0F2elJKQWcxOWROSHBidThteXc1ckc5bHJ4MWhqa3JRREVRLzA?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cDovL21tYml6LnFwaWMuY24vbW1iaXpfcG5nL294QUhld2FMMnJpYXc5YWI2dHNBSk40REFpYVp0Y081WUg1WHV1YjEyQmdrakZjSFRSWTByMTdGcUQ4U3FNTEJ0MTIwODBDQkEyaWN5dmZzeTY2ZDVqdkp3LzA?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cDovL21tYml6LnFwaWMuY24vbW1iaXpfanBnL2IwS2J4MTd3Z1hGUGFSakttNkw3eWxzcmljSG9WUmppYlRmQ3hSR2g5c3JueFpQNG5yMlBkVjlYaHNaWEI1VmxaNzlwa3h0RmI4c3BuaWNlV0E2V3V1bFZBLzY0MA?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cDovL21tYml6LnFwaWMuY24vbW1iaXpfZ2lmL294QUhld2FMMnJqSXZ2dmFwVzVFVm0wWEMzV2xUMXg2UWdQUUJOZXczQndDalh4Qnhya1VEbHBjd09VeFB4eVVUSTZpY1RpYUhzZzVONG81emRUcFRqWVEvMA?x-oss-process=image/format,png