MySQL中的分层查询.(MySQL 的等价物连接)

2022-01-15 00:00:00 mariadb 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.

相关文章