MySQL递归查询

MySQL8.0已经支持CTE递归查询，举例说明

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);

SQL建表语句

在EMP表中存在着树形结构，比如员工JONES,他是SOCTT的mgr，而SCOTT又是ADAMS的mgr，即JONES→SOCTT→ADAMS

由于树的深度不确定，用递归可以避免这个问题，MySQL会帮我们遍历整个树形结构，当遍历结束，会自动退出递归。

问题1：查询员工JONES的所有下属员工，包括下属的下属

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的所有上级

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 |
+-------+------+