MySQL8.0已经支持CTE递归查询,举例说明
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE TABLE EMP (EMPNO integer NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR integer, HIREDATE DATE, SAL integer, COMM integer, DEPTNO integer); INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800, NULL, 20); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20', 1600,300, 30); INSERT INTO EMP VALUES (7521,'WARD', 'SALESMAN',7698, '1981-2-22', 1250,500, 30); INSERT INTO EMP VALUES (7566,'JONES','MANAGER', 7839, '1981-4-2',2975, NULL, 20); INSERT INTO EMP VALUES (7654,'MARTIN', 'SALESMAN',7698, '1981-9-28', 1250, 1400, 30); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER', 7839, '1981-5-1',2850, NULL, 30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER', 7839, '1981-6-9',2450, NULL, 10); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST', 7566, '1982-12-9', 3000, NULL, 20); INSERT INTO EMP VALUES (7839,'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10); INSERT INTO EMP VALUES (7844,'TURNER', 'SALESMAN',7698, '1981-9-8',1500,0, 30); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788, '1983-1-12', 1100, NULL, 20); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698, '1981-12-3', 950, NULL, 30); INSERT INTO EMP VALUES (7902,'FORD', 'ANALYST', 7566, '1981-12-3',3000, NULL, 20); INSERT INTO EMP VALUES (7934,'MILLER', 'CLERK',7782, '1982-1-23', 1300, NULL, 10);
在EMP表中存在着树形结构,比如员工JONES,他是SOCTT的mgr,而SCOTT又是ADAMS的mgr,即JONES→SOCTT→ADAMS
由于树的深度不确定,用递归可以避免这个问题,MySQL会帮我们遍历整个树形结构,当遍历结束,会自动退出递归。
问题1:查询员工JONES的所有下属员工,包括下属的下属
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
with recursive temp(ename,empno) as ( select ename,empno from emp where ename = 'JONES' union all select p.ename,p.empno from temp,emp p where temp.empno = p.mgr ) select empno,ename from temp;
+-------+-------+
| empno | ename |
+-------+-------+
| 7566 | JONES |
| 7788 | SCOTT |
| 7902 | FORD |
| 7369 | SMITH |
| 7876 | ADAMS |
+-------+-------+
问题2:查询员工ADAMS的所有上级
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
with recursive temp(ename,mgr) as ( select ename,mgr from emp where ename = 'ADAMS' union all select p.ename,p.mgr from temp,emp p where temp.mgr = p.empno ) select ename,mgr from temp;
+-------+------+
| ename | mgr |
+-------+------+
| ADAMS | 7788 |
| SCOTT | 7566 |
| JONES | 7839 |
| KING | NULL |
+-------+------+