SQL 递归查询范例应用

前段时间因为单位OA的替换，需要做一些上下级的递归查询，一直没找到办法。网上找了下资料，心中大概有个数了。

先创建2个表

CREATE TABLE dept(  
deptno INT PRIMARY KEY,  
dname VARCHAR(20),  
loc VARCHAR(20), 
SJdeptno INT 
)  

INSERT INTO dept VALUES (1,'英三厂','李军','')
INSERT INTO dept VALUES (10,'制造处','小马',1)  
INSERT INTO dept VALUES (11,'研发处','小明',1)  
INSERT INTO dept VALUES (21,'技术部','王虎',10)  
INSERT INTO dept VALUES (22,'品管部','赵敏',10)  
INSERT INTO dept VALUES (23,'软件部','吴互',11)  
INSERT INTO dept VALUES (24,'硬件部','汤米',11) 

CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(20) NOT NULL,


insert into emp values(7369,'李军','CLERK',7902,'1980-12-17',1640,NULL,1);  
insert into emp values(7499,'小马','SALESMAN',7698,'1981-2-20',11400,300,10);  
insert into emp values(7521,'小明','SALESMAN',7698,'1981-2-22',5200,500,11);  
insert into emp values(7566,'王虎','MANAGER',7839,'1981-4-2',7015,NULL,21);  
insert into emp values(7654,'赵敏','SALESMAN',7698,'1981-9-28',5200,1400,22);  
insert into emp values(7698,'吴互','MANAGER',7839,'1981-5-1',5900,NULL,23);  
insert into emp values(7782,'汤米','MANAGER',7839,'1981-6-9',2470,NULL,24); 

其中emp表中的DEPTNO和dept表中的deptno是对应的关系，我们想查询一个员工的所属的部门层级架构

WITH B1 AS(
SELECT deptno,dname,dname 一级部门,''二级部门,'' 三级部门,'' 四级部门,'' 五级部门 
FROM dept WHERE deptno=1 ),  /* deptno=1作为root node，这是递归查询的起始点 */ 

B2 AS(
SELECT B.deptno,B.dname,B1.一级部门,B.dname 二级部门,'' 三级部门,'' 四级部门,'' 五级部门 
FROM B1 JOIN dept B ON B.SJdeptno=B1.deptno ), /* 将上面的B1表和dept表（B）作连接查询，得出B2表 */

B3 AS(
SELECT B.deptno,B.dname,B2.一级部门,B2.二级部门,B.dname 三级部门,'' 四级部门,'' 五级部门 
FROM B2 JOIN dept B ON B.SJdeptno=B2.deptno ), /* 将上面的B2表和dept表（B）作连接查询，得出B3表 */

B AS(
SELECT * FROM B1
UNION ALL
SELECT * FROM B2
UNION ALL
SELECT * FROM B3)
select * from B  /* 将B1，B2，B3进行联合查询 */ 

B1，B2，B3 分别查询的结果，便于理解。

WITH B1 AS(
SELECT deptno,dname,dname 一级部门,''二级部门,'' 三级部门,'' 四级部门,'' 五级部门 FROM dept WHERE deptno=1 ),

B2 AS(
SELECT B.deptno,B.dname,B1.一级部门,B.dname 二级部门,'' 三级部门,'' 四级部门,'' 五级部门 
FROM B1 JOIN dept B ON B.SJdeptno=B1.deptno ),

B3 AS(
SELECT B.deptno,B.dname,B2.一级部门,B2.二级部门,B.dname 三级部门,'' 四级部门,'' 五级部门 
FROM B2 JOIN dept B ON B.SJdeptno=B2.deptno ),

B AS(
SELECT * FROM B1
UNION ALL
SELECT * FROM B2
UNION ALL
SELECT * FROM B3)

select ename,一级部门,二级部门,三级部门,四级部门,五级部门 from B JOIN emp ON emp.DEPTNO=B.deptno

用一个连接查询查询员工的组织架构

这样员工的组织架构就很清晰了。