树形查询本质就是递归算法(自己调用自己),Oracle很早就支持了树形查询
MySQL中可以使用 WITH RECURSIVE 来实现树形查询
比如在Oracle中scott账户下,找出员工编号为7369所有的上级:
SQL> select empno, ename, job, mgr, level
2 from emp
3 start with empno = 7369
4 connect by empno = prior mgr;
EMPNO ENAME JOB MGR LEVEL
----- ---------- --------- ----- ----------
7369 SMITH CLERK 7902 1
7902 FORD ANALYST 7566 2
7566 JONES MANAGER 7839 3
7839 KING PRESIDENT 4
在MySQL8.0.19中:
mysql> WITH RECURSIVE x(empno,ename,job,mgr,lv) AS
-> (SELECT empno,ename,job,mgr,1 lv from emp where empno=7369
-> UNION ALL
-> select t1.empno,t1.ename,t1.job,t1.mgr,lv+1 from emp t1,x where t1.empno=x.mgr
-> )
-> select * from x;
+-------+-------+-----------+------+------+
| empno | ename | job | mgr | lv |
+-------+-------+-----------+------+------+
| 7369 | SMITH | CLERK | 7902 | 1 |
| 7902 | FORD | ANALYST | 7566 | 2 |
| 7566 | JONES | MANAGER | 7839 | 3 |
| 7839 | KING | PRESIDENT | NULL | 4 |
+-------+-------+-----------+------+------+
4 rows in set (0.00 sec)