级联查询

--创建组织表，并初始化数据

DROP TABLE  COM_ORGANIZATION ;
CREATE TABLE COM_ORGANIZATION (
    org_id      NUMBER NOT NULL CONSTRAINT organization_id_pk PRIMARY KEY,
    org_name    VARCHAR2(255),
    org_pid     NUMBER NOT NULL 
);

insert into COM_ORGANIZATION values(1,'SSC GROUP OF COMPANIES',0);
insert into COM_ORGANIZATION values(2,'LOGISTICS',1);
insert into COM_ORGANIZATION values(3,'FINANACE',1);
insert into COM_ORGANIZATION values(4,'TECHNOLOGIES',1);
insert into COM_ORGANIZATION values(5,'Shanghai Branch',2);
insert into COM_ORGANIZATION values(6,'Shenzhen Branch',2);


--级联查询
SELECT co.org_id, co.org_name, co.org_pid  , LEVEL
   FROM COM_ORGANIZATION co
   START WITH co.org_pid = 0 
   CONNECT BY PRIOR org_id = org_pid;


org_id	org_name				org_pid	level	
1		SSC GROUP OF COMPANIES	0		1
2		LOGISTICS				1		2
5		Shanghai Branch			2		3
6		Shenzhen Branch			2		3
3		FINANACE				1		2
4		TECHNOLOGIES			1		2