在 Oracle SQL 中连接 [英] Connect by in Oracle SQL

问题描述

假设我们有以下表格

create table Employee(
  2    EMPNO         NUMBER(3),
  3    ENAME         VARCHAR2(15 BYTE),
  4    HIREDATE      DATE,
  5    ORIG_SALARY   NUMBER(6),
  6    CURR_SALARY   NUMBER(6),
  7    REGION        VARCHAR2(1 BYTE),
  8    MANAGER_ID    NUMBER(3)
  9  )

和

create table job (
  2    EMPNO         NUMBER(3),
  3    jobtitle      VARCHAR2(20 BYTE)
  4  )
  5  /

我对以下查询的功能感兴趣

I am interested in what the below query does

SELECT empno, manager_id, ename
  2  FROM employee
  3  START WITH empno = 1
  4  CONNECT BY PRIOR empno = manager_id;

据我所知，此代码从员工中选择 empno,manager_id,ename，它从 empno= 的这一行开始选择1，但无法理解这一行的作用:

As I understood this code selects empno,manager_id,ename from employee, it starts selection from this row where empno=1, but could not understand what this line does:

CONNECT BY PRIOR empno = manager_id;

这是否与:

where empno=manager_id?

推荐答案

CONNECT BY PRIOR empno = manager_id;

这将产生递归.属于下一个较低层次级别的所有记录都将返回.这将为所有经理及其各自的下属工作返回一个从上到下的层次结构.

This will produce the recursion. All records that are part of the next lower hierarchical level will return. This will return a hierarchy from top to bottom for all managers and their respective under working subordinates.

30 (manager_id)
   12 
   5 (manager_id)
      1
      7
20 (manager_id)
   15
   10

这篇关于在 Oracle SQL 中连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！