带有递归自我JOIN的MySQL查询 [英] Mysql query with recursive self JOIN

问题描述

我正在尝试使用递归自我联接编写查询.我的表格如下:

I am trying to write a query with a recursive self join. My tables are something as below:

表1

EMP_ID      Name
1          Manger1 
2          Manger2
3          Manger3
4          Employ1
5          Employ2

表2

Par_EMP_ID   EMP_ID   
1                2
2                3
3                4
3                5  

在上表中，Manager1，Manager2，...，Employe2是公司的雇员，其中Manager1是部门负责人.Manager2向Manager1，Manager3报告正在向Manager2报告，此关系将保留在table2中，其中Par_EMP_ID是reportii的EMP_ID，列EMP_ID是报告者的ID.现在我想要的结果如下:

Here in above tables the Manager1, Manager2, ..., Employe2 are employees in a company where Manager1 is Head of Dept. Manager2 is reporting to Manager1, Manager3 is reporting to Manager2, this relation ship is maintained in table2 where Par_EMP_ID is EMP_ID of reportii and column EMP_ID is id of reporter. Now I want result as below:

Manager1      Manager2   Manager3  Employe1
Manager1      Manager2   Manager3  Employe2

推荐答案

更新:

根据您的规格，这是解决方案:

According to your specs, here's the solution:

SELECT  e.names member, d.name child
    FROM MEMBERS d INNER JOIN
        (SELECT a.id, GROUP_CONCAT(c.name) NAMES
            FROM MEMBERS a
    INNER JOIN RELATIONSHIP b ON a.id = b.MEM_ID
INNER JOIN MEMBERS c ON c.id = b.PAR_MEM_ID
GROUP BY a.id) e ON e.id = d.id

结果:

|             MEMBER |        CHILD |
|--------------------|--------------|
| Great Grand Father | Grand Father |
|       Grand Father |       Father |
|             Father |       Child1 |
|             Father |       Child2 |

注意:结果可能取决于您的样本数据.而且我还更新了下面的SQLFiddle，因此您可能需要检查一下.

Note:the results may depend on your sample data. And I updated also the SQLFiddle below so you might want to check that out.

这是 SQLFiddle .

这篇关于带有递归自我JOIN的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！