I am trying to write a query with a recursive self join. My tables are something as below:
我正在尝试使用递归自联接编写查询。我的表格如下:
Table1
EMP_ID Name
1 Manger1
2 Manger2
3 Manger3
4 Employ1
5 Employ2
Table2
Par_EMP_ID EMP_ID
1 2
2 3
3 4
3 5
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,...,Employe2是Manager1是部门主管的公司中的员工.Manager2向Manager1报告,Manager3向Manager2报告,此关系在表2中维护,其中Par_EMP_ID是EMP_ID reportii和列EMP_ID是记者的id。现在我想要结果如下:
Manager1 Manager2 Manager3 Employe1
Manager1 Manager2 Manager3 Employe2
1 个解决方案
#1
1
UPDATE:
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
Result:
| MEMBER | CHILD |
|--------------------|--------------|
| Great Grand Father | Grand Father |
| Grand Father | Father |
| Father | Child1 |
| Father | Child2 |
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,所以你可能想要检查出来。
Here's the SQLFiddle.
这是SQLFiddle。
#1
1
UPDATE:
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
Result:
| MEMBER | CHILD |
|--------------------|--------------|
| Great Grand Father | Grand Father |
| Grand Father | Father |
| Father | Child1 |
| Father | Child2 |
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,所以你可能想要检查出来。
Here's the SQLFiddle.
这是SQLFiddle。