每个经理直接或间接工作的员工的打印数量（oracle）(print number of employee working directly or indirectly under each manag

每个经理直接或间接工作的员工的打印数量（oracle）(print number of employee working directly or indirectly under each manager(oracle))

我有一个像Employee一样的表

managername varchar2(20), salary number(10,2), empname varchar2(20), Constraint [PK_Emp] Primary key (empname), Constraint [FK_Emp] Foreign key (managername) references employee (empname) on delete cascade mangername salary empname ------ ------ -------------- "" 1000 Sumanta Sumanta 1000 Arpita Sumanta 1000 Pradip Arpita 1000 Sujon Sujon 1000 Arpan Sujon 1000 Jayanti

我想了解直接或间接在每位经理下工作的所有员工

I have a Employee table like

managername varchar2(20), salary number(10,2), empname varchar2(20), Constraint [PK_Emp] Primary key (empname), Constraint [FK_Emp] Foreign key (managername) references employee (empname) on delete cascade mangername salary empname ------ ------ -------------- "" 1000 Sumanta Sumanta 1000 Arpita Sumanta 1000 Pradip Arpita 1000 Sujon Sujon 1000 Arpan Sujon 1000 Jayanti

I want to get count of all the employees that directly or indirectly work under each manager

最满意答案

您可以使用递归CTE执行此操作：

with cte(managername, empname, level) as ( select managername, empname, 0 from employee union all select e.managername, cte.empname, level + 1 from cte join employee e on cte.managername = e.empname ) select managername, count(*) from cte group by managername;

这应该在11g第2版中工作。虽然基本相同的代码在SQL Server中工作，但它似乎在SQL Fiddle中有问题。

You can do this with a recursive CTE:

with cte(managername, empname, level) as ( select managername, empname, 0 from employee union all select e.managername, cte.empname, level + 1 from cte join employee e on cte.managername = e.empname ) select managername, count(*) from cte group by managername;

This should work in 11g Release 2. It seems to have trouble in SQL Fiddle although basically the same code works in SQL Server.