MySQL中的分层查询. (以等效方式连接MySQL) [英] Hierarchical Query in MySQL. (connect by equivalent for MySQL)
问题描述
我在表中有三个字段,用于定义MySQL数据库中存在的层次关系.
I have three fields in a table that define a hierarchical relationship present in a MySQL database.
Table Name : tb_corp
--------------------------------------------
comp_code | incharge_comp_Code | mngr_emp_no
A | | 111
--------------------------------------------
B | A |
--------------------------------------------
C | B |
--------------------------------------------
如何编写查询以获取mngr_emp_no = 111负责的所有comp_code.根据上表,111负责三个公司(A,B和C).原因是A公司负责B公司,B公司负责C公司,结果A也负责C公司. (A-> B)(B-> C)==(A-> C)
How do I write a query to obtain all the comp_code that mngr_emp_no = 111 is in charge. According to the table above, 111 is in charge of three companies(A, B, and C). The reason is that A company is in charge of B company and B company is in charge of C company as a result A is also in charge of C company. (A -> B) (B -> C) == (A -> C)
推荐答案
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.
这篇关于MySQL中的分层查询. (以等效方式连接MySQL)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!