Oracle数据库 Connect By 语句可以查询存在级联记录表的数据。例如: 员工表(员工ID,主管ID,员工名字),可以查询员工上级主管名字等信息,比较方便
语法
- CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
- START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]
注:condition 的条件语句一般使用 prior (ps:prior只修饰了字段不是整个表达式) 修饰该字段为 当前节点字段。可以根据下面例子理解
数据准备
CREATE TABLE Employee (
Eid INT primary key,
Leaderid INT,
Ename VARCHAR (50)
);INSERT INTO Employee VALUES(10000,0,'0name');
INSERT INTO Employee VALUES(10001,10000,'1name');
INSERT INTO Employee VALUES(10002,10000,'2name');
INSERT INTO Employee VALUES(10003,10001,'3name');
INSERT INTO Employee VALUES(10004,10001,'4name');
INSERT INTO Employee VALUES(10005,10002,'5name');
INSERT INTO Employee VALUES(10006,10002,'6name');
INSERT INTO Employee VALUES(10007,10003,'7name');
INSERT INTO Employee VALUES(10008,10003,'8name');
INSERT INTO Employee VALUES(10009,10004,'9name');
INSERT INTO Employee VALUES(10010,10005,'10name');
数据表级联树结构
例1:请求 10003号员工的所有下级名字
SELECT
Employee.*, PRIOR Ename AS Leadername
FROM
Employee START WITH Eid = 10003 CONNECT BY PRIOR Eid = Leaderid
结果
过程
以 数据表级联树结构为参考,找到 START WITH 语句条件为 Eid = 10003 的节点,记为节点10003,为首节点。然后以Connect By修饰的条件(PRIOR Eid = Leaderid)查找下一个节点;prior修饰字段的值为当前节点字段值(10003)。下个节点查找方法为:prior修饰的字段(Eid)的值为当前节点 节点10003 的值,where条件变为10003=Leaderid; 即查找下个节点为 10003=Leaderid 的节点。即找到 10007 10008两个节点。再以找到俩个节点为起始节点。类推下面的节点( 100007=Leaderid ;100008=Leaderid)等等。
思考:
如果条件为以下条件,将会有什么结果
SELECT
Employee.*, PRIOR Ename AS Leadername
FROM
Employee START WITH Eid = 10001 CONNECT BY PRIOR Eid = Leaderid
ps:个人仅仅做了一小部分的资料查询和个人测试,只作为参考理解。如有不足之处,请留言一起探讨,共同进步!