oracle递归查询 start with...connect by

一、start with.....connect by递归查询

建表语句：

Sql代码  

1. CREATE TABLE D_ZONECODE  
2. (  
3.     ID VARCHAR2(36) NOT NULL UNIQUE,  
4.     ZONECODE VARCHAR2(6) NOT NULL,  
5.     SUPERCODE VARCHAR2(6) NOT NULL,  
6.     ZONELLEVEL VARCHAR2(2) NOT NULL,  
7.     ZONENAME VARCHAR2(60) NOT NULL  
8. );  

 插入数据语句：

Sql代码  

1. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('1', '370000', '000000', '01', '山东省');  
2. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('2', '370100', '370000', '02', '济南市');  
3. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('3', '370102', '370100', '03', '历下区');  
4. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('4', '370103', '370100', '03', '市中区');  
5. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('5', '370104', '370100', '03', '槐荫区');  
6. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('6', '370105', '370100', '03', '天桥区');  
7. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('7', '370112', '370100', '03', '历城区');  
8. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('8', '370113', '370100', '03', '长清区');  
9. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('9', '370114', '370100', '03', '高新区');  
10. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('10', '370124', '370100', '03', '平阴县');  
11. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('11', '370125', '370100', '03', '济阳县');  
12. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('12', '370126', '370100', '03', '商河县');  
13. INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('13', '370181', '370100', '03', '章丘市');  

 

root向树末梢查询：

 

Sql代码  

1. select * from d_zonecode  
2.  start with id=’370000’  
3.  connect by prior zonecode = supercode;  

  

 树末梢向ROOT查询：

 

Sql代码  

1. select * from d_zonecode  
2.  start with zonecode = '370100'  
3.  connect by prior supercode = zonecode;  

 

 

附：

start with指明从哪里开始遍历树

connect by 就是指明父子关系,注重PRIOR位置

CONNECT_BY_ROOT: 提供获取根节点记录的字段信息。

 

二、 今天客户突然要一个表格，这里面包括两个表的总分关系，比如我要显示部门同时和该部门下的所有人员用一条记录显示，在网上搜到一个例子，记录下方便使用。

 

 

 

建表语句：

 

Sql代码  

1. /* Create Tables */  
2. CREATE TABLE DEPT  
3. (  
4.     ID VARCHAR2(36) NOT NULL UNIQUE,  
5.     DEPTID VARCHAR2(4) NOT NULL UNIQUE,  
6.     DEPTNAME VARCHAR2(60) NOT NULL  
7. );  
8. CREATE TABLE D_USER  
9. (  
10.     ID VARCHAR2(36) NOT NULL UNIQUE,  
11.     USERID VARCHAR2(6) NOT NULL UNIQUE,  
12.     USERNAME VARCHAR2(60) NOT NULL,  
13.     DEPTID VARCHAR2(4) NOT NULL  
14. );  

 

插入数据语句：

 

Sql代码  

1. -- 部门表  
2. INSERT INTO dept (id, deptid, deptname) VALUES ('1', '0001', '市场部');  
3. INSERT INTO dept (id, deptid, deptname) VALUES ('2', '0002', '开发部');  
4. INSERT INTO dept (id, deptid, deptname) VALUES ('3', '0003', '项目部');  
5. -- 用户表  
6. INSERT INTO D_USER (id, userid, username, deptid) VALUES ('1', '100001', '张肃宁', '0001');  
7. INSERT INTO D_USER (id, userid, username, deptid) VALUES ('2', '100002', '王济南', '0002');  
8. INSERT INTO D_USER (id, userid, username, deptid) VALUES ('3', '100003', '赵临沂', '0001');  
9. INSERT INTO D_USER (id, userid, username, deptid) VALUES ('4', '100004', '金淄博', '0003');  
10. INSERT INTO D_USER (id, userid, username, deptid) VALUES ('5', '100005', '李德州', '0002');  
11. INSERT INTO D_USER (id, userid, username, deptid) VALUES ('6', '100006', '周济宁', '0001');  
12. INSERT INTO D_USER (id, userid, username, deptid) VALUES ('7', '100007', '姜潍坊', '0003');  
13. INSERT INTO D_USER (id, userid, username, deptid) VALUES ('8', '100008', '万青岛', '0001');  

 

查询：

 

Sql代码  

1. select * from dept t;  
2. select * from d_user t;  

 

 

 
 测试sql如下：

 

Sql代码  

1. select username from (  
2. select row_number() over(order by lv desc) id,username,deptid from (  
3. select level lv, replace(sys_connect_by_path(username,','),',',',') username,deptid from(  
4. select deptid,username,row_number() over(order by username) id from  
5. (  
6. select a.deptid,a.deptname,b.username  
7. from dept a,d_user b  
8. where a.deptid = b.deptid  
9. )  
10. ) connect by prior id = id-1 )) where id = 1;  

 

 

要是需要和部门连接查询，我采用建立一个方法，然后查询部门记录时调用该方法，传部门id这个参数进去。

 

Sql代码  

1. --方法建立：  
2. create or replace function getUsername(oc_deptid in varchar2  
3.                                   ) return varchar2 is  
4.   oc_result varchar(300);  
5.   oc_username varchar2(300); -- 取值  
6. begin  
7. --查询用户名称  
8.   select username into oc_result from (  
9. select row_number() over(order by lv desc) id,username,deptid from (  
10. select level lv, replace(sys_connect_by_path(username,','),',',',') username,deptid from(  
11. select deptid,username,row_number() over(order by username) id from  
12. (  
13. select a.deptid,a.deptname,b.username  
14. from dept a,d_user b  
15. where a.deptid = b.deptid and a.deptid = oc_deptid  
16. )  
17. ) connect by prior id = id-1 )) where id = 1;  
18.   oc_username:=oc_result;  
19.   if oc_result is NULL then  
20.      oc_username := '';  
21.   else  
22.      oc_username := SUBSTR(oc_result,INSTR(oc_result,',')+1,LENGTH(oc_result)-1);  
23.   end if;  
24.   return oc_username;  
25. end;  

 

查询sql:

 

 

Sql代码  

1. select t.deptid,t.deptname,getUsername(t.deptid) as username from dept t;  

 

 

附（CONNECT_BY_ROOT使用）：

 

Sql代码  

1. select zonecode,zonename,CONNECT_BY_ROOT(zonecode) as root_code  from d_zonecode  
2. start with zonecode = '370100'  
3. connect by prior zonecode = supercode and zonecode != supercode;  
4. select zonecode,CONNECT_BY_ROOT(zonecode) as root_code from d_zonecode  
5. start with zonecode = '370114'  
6. connect by prior supercode = zonecode and zonecode != supercode;  
7. select CONNECT_BY_ROOT(t.zonecode) as ROOT from d_zonecode t  
8. where t.zonecode = '370100'  
9. start with t.zonecode = t.supercode  
10. connect by prior t.zonecode = t.supercode and t.zonecode != t.supercode;