SQL查询以获取主管层次结构列表。员工->主管->主管

问题描述

我有两个表Employee和Department这幅图显示了每个员工的经理。我想编写一个SQL查询，为我提供所有主管（经理，经理的经理..）的列表。

I have two tables Employee and Department this image shows the manager of every employee. I want to write a SQL query that gives me a list of all the supervisor (Manager, Manager of Manager..).

我只希望显示一列授予特定雇员时的主管名单。

I just want a single column that displays a list of supervisor when given a particular employee.

例如如果我给员工id = 202，那么我应该收到200,130

E.g. If I give employee id = 202 then I should receive 200,130

 |supervisor |
 +-----------+
 |   200     |
 |   130     |

我有此查询

WITH emp_dept as(
SELECT employee_id,manager_id
FROM employee,department
WHERE employee.dept_id= department.dept_id
   )

 WITH recursive p as (
    select e1.employee_id, e1.manager_id
    from   emp_dept e1
    where    employee_id = 202

    union all

   select e2.employee_id , e2.manager_id
   from   p
   join   emp_dept e2 ON e2.employee_id = p.manager_id

)
select manager_id
from   p

`

能够使用它。我正在使用pgadmin4。

I am not able to use it. I am using pgadmin4.

如果有人可以帮助我进行此查询，我将不胜感激

If anyone could help me with this query I would greatly appreciate it

推荐答案

这是我问题的解决方案

  with recursive p as (
WITH emp_dept as(
SELECT employee_id,manager_id
FROM employee,department
WHERE employee.dept_id= department.dept_id
)

select e1.employee_id, e1.manager_id
from   emp_dept e1
where  e1.employee_id = 202

union

select e2.employee_id , e2.manager_id
from   p
join   emp_dept e2 ON e2.employee_id = p.manager_id

)
select manager_id
  from   p

这篇关于SQL查询以获取主管层次结构列表。员工->主管->主管的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！