当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Finding sub reporting employees in mysql

问题描述:

employee id reporting_head_id

----------------------------------

1 0

2 1

3 1

4 2

5 2

6 3

7 4

8 5

9 6

Suppose I want all employees reporting under emp_id=1.

So I can get it from

select * from employee where reporting_head_id=1

It will return 2 and 3.

But I also want to search employees those who are reporting to 2 and 3 and so on. And employees reporting to 2 and 3, suppose to those reporting employees also some employees report so I also want their information and so on.

I want all the employees for the given reporting_head_id plus those employees who report to the employees reporting to the given id.

Now if I search for employees who report to reporting_head_id 1, employees 2,3,4,5,6,7,8,9 should be returned.

Please can you provide me solution. I am new to SQL.

I have tried this query

SELECT *

FROM employee

WHERE reporting_head_id = 1

or reporting_head_id = any (select id from employee where reporting_head_id = 1)

so it returns 2,3,4,5,6

But how to find those who reports to 4, 5, 6 and so on

网友答案:

Other than a foreach loop which then querys for your addition results I'm not sure.

Try:

"SELECT * FROM employee WHERE reporting_head_id=1"

Then take the result and:

foreach($employees as $employee)
{
    "SELECT * FROM employee WHERE reporting_head_id=".$employee;
}
网友答案:

I have not tried it but I believe this is an example of what you are looking for: http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

Unfortunately, I don't think this is available in mySQL. However I found the two below questions that I think are similar and would give you a jumping off point:

select ancestors using query like a recursive function

Recursive MySQL query

网友答案:

This will give some idea on the recursive function. We can make the code even simpler once we get the idea.

$qry = "SELECT * FROM employee WHERE reporting_head_id=1";
$rec = mysql_query($qry);
while($row = mysql_fetch_array($rec)){
    $id = $row['empid'];
    $arr[] = $id;
    foreach(rec($id) as $key=>$val){
        $arr[] = $val;
        $arr[] = rec($val);
        rec($val); 
    }
}

function rec($id){
    $qry = "SELECT * FROM emp WHERE reporting_head_id=$id";
    $rec = mysql_query($qry);
    while($row = mysql_fetch_array($rec)){
        $arec[] = $row['empid']; 
    }
    return $arec;

}
print_r($arr); //This array will contain the  reporting_head_id plus those employees who report to the employees reporting to the given id.
分享给朋友:
您可能感兴趣的文章:
随机阅读: