更新时间:2022-04-30 03:20:31
MySQL 没有原生分层查询支持.
There is no native hierarchical query support in MySQL.
对于要遍历的有限数量的级别,我们可以编写查询来获取每个级别的结果,并将结果与 UNION ALL
运算符组合.
For a finite number of levels to be traversed, we can write queries that get result for each level, and combine the results with a UNION ALL
operator.
或者,我们可以编写一个 MySQL 存储程序(过程)以实现更递归的方法.
Or, we can write a MySQL stored program (procedure) for a more recursive approach.
作为使用原生 SQL 查询的方法示例:
As an example of approach using a native SQL query:
SELECT t0.comp_code
FROM tb_corp t0
WHERE t0.mgr_emp_no = 111
UNION ALL
SELECT t1.comp_code
FROM tb_corp t0
JOIN tb_corp t1 ON t1.incharge_comp_code = t0.comp_code
WHERE t0.mgr_emp_no = 111
UNION ALL
SELECT t2.comp_code
FROM tb_corp t0
JOIN tb_corp t1 ON t1.incharge_comp_code = t0.comp_code
JOIN tb_corp t2 ON t2.incharge_comp_code = t1.comp_code
WHERE t0.mgr_emp_no = 111
UNION ALL
SELECT t3.comp_code
FROM tb_corp t0
JOIN tb_corp t1 ON t1.incharge_comp_code = t0.comp_code
JOIN tb_corp t2 ON t2.incharge_comp_code = t1.comp_code
JOIN tb_corp t3 ON t3.incharge_comp_code = t2.comp_code
WHERE t0.mgr_emp_no = 111
等等.这种方法可以扩展到 t4, t5, t6, ... 到一些(合理的)有限数量的级别.
etc. This approach can be extended to t4, t5, t6, ... down to some (reasonable) finite number of levels.
对于更递归的方法,可以编写一个 MySQL 存储程序(PROCEDURE).
For a more recursive approach, a MySQL stored program (PROCEDURE) can be written.