Oracle中start with…connect by prior子句用法 connect by 是结构化查询中用到的，其基本语法是： select … from tablename start with 条件1 connect by 条件2 where 条件3; 例： select * from table start with org_id = ‘HBHqfWGWPy’ connect by prior org_id = parent_id; 简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段: org_id，parent_id那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。 用上述语法的查询可以取得这棵树的所有记录。 其中： 条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。 条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id；就是说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。 条件3 是过滤条件，用于对返回的所有记录进行过滤。
准备数据：
create table t_dept (
deptid number not null primary key, dentname_en varchar2(200) not null, deptname_cn varchar2(200) not null, deptcode varchar2(200) not null, parent_id number(10) default -1); create sequence seq_dept start with 100 increment by 1 minvalue 1 maxvalue 1000000 nocycle cache 20; create index t_index_parent_id on scott.t_dept(parent_id); create index t_index_dept_code on t_dept(deptcode); --插入测试数据; insert into scott.t_dept values(seq_dept.nextval,'Front Office Department','前台部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Financial Department','财务部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Logistics Department','后勤部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Recruiting Department','招聘部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Development department','开发部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Dngineering department','工程部','0'||seq_dept.nextval,-1); insert into scott.t_dept values(seq_dept.nextval,'Front Office Department_001','前台接待分部','0'||seq_dept.nextval,100); insert into scott.t_dept values(seq_dept.nextval,'Front Office Department_002','前台分流部','0'||seq_dept.nextval,100); insert into scott.t_dept values(seq_dept.nextval,'Front Office Department_003','前台引导客流部','0'||seq_dept.nextval,100); insert into scott.t_dept values(seq_dept.nextval,'Receptionist Department_001','前台接待普通客户部','0'||seq_dept.nextval,106); insert into scott.t_dept values(seq_dept.nextval,'Receptionist Department_002','前台接待中级客户部','0'||seq_dept.nextval,106); insert into scott.t_dept values(seq_dept.nextval,'Receptionist Department_003','前台接待高级客户部','0'||seq_dept.nextval,106); insert into scott.t_dept values(seq_dept.nextval,'Sweep the toilet Department','扫厕所分部','0'||seq_dept.nextval,102); insert into scott.t_dept values(seq_dept.nextval,'Logistical support Department','后勤保障部','0'||seq_dept.nextval,102); insert into scott.t_dept values(seq_dept.nextval,'Clean the health','打扫卫生分部','0'||seq_dept.nextval,102); insert into scott.t_dept values(seq_dept.nextval,'Men lavatory men toilet','打扫男厕所分部','0'||seq_dept.nextval,112); insert into scott.t_dept values(seq_dept.nextval,'Women lavatory','打扫女厕所分部','0'||seq_dept.nextval,112); insert into scott.t_dept values(seq_dept.nextval,'PHP Development department','PHP开发部','0'||seq_dept.nextval,104); insert into scott.t_dept values(seq_dept.nextval,'.Net Development department','.NET开发部','0'||seq_dept.nextval,104); insert into scott.t_dept values(seq_dept.nextval,'Java Development department','Java开发部','0'||seq_dept.nextval,104); insert into scott.t_dept values(seq_dept.nextval,'C++ Development department','C++开发部','0'||seq_dept.nextval,104); insert into scott.t_dept values(seq_dept.nextval,'Python Development department','Python开发部','0'||seq_dept.nextval,104); insert into scott.t_dept values(seq_dept.nextval,'Java Development department_001','Java前台开发部','0'||seq_dept.nextval,123); insert into scott.t_dept values(seq_dept.nextval,'Java Development department_002','Java后台开发部','0'||seq_dept.nextval,123);commit;
--根据子部门查询出父部门(当prior parent = id时，数据库会跟据当前的parent来迭代出与当前的parent相同的id的记录，所以查询出来的结果就是所有的父类结果)。 select deptid, dentname_en, deptname_cn, deptcode, parent_id, level from scott.t_dept start with deptid = 109 connect by prior parent_id = deptid; 等效于: --根据子部门查询出父部门 select deptid, dentname_en, deptname_cn, deptcode, parent_id, level from scott.t_dept start with deptid = 109 connect by deptid = prior parent_id;
============================================================================
--根据父部门查询出子部门(当parent = prior id时，数据库会根据当前的id迭代出parent与该id相同的记录，所以查询的结果是迭代出了所有的子类记录)； select deptid, dentname_en, deptname_cn, deptcode, parent_id, level from scott.t_dept start with deptid = 100 connect by parent_id = prior deptid ; 等效于: --根据父部门查询出子部门 select deptid,dentname_en,deptname_cn,deptcode,parent_id,level from scott.t_dept dept start with deptid = 100 connect by prior dept.deptid = dept.parent_id;
=========================================================================
CONNECT BY ---------------LEVEL---------------:查询第几层
SELECT DEPTID,LEVEL,PARENT_ID,T_DEPT.* FROM T_DEPT START WITH DEPTID =102 CONNECT BY PRIOR DEPTID = PARENT_ID;
