вложенный запрос для получения количества сотрудников под менеджерами

Question

create table employee (
Id int,
ManagerId int);

insert into employee values
(1, 3),
(2, 3),
(3, 6),
(4, 7),
(5, 7),
(6, 8),
(7, 6);

select * from employee;

вот таблица:

https://i.stack.imgur.com/22JPA.png

как написать запрос, чтобы получить менеджеров с количеством подчиненных. Обратите внимание на вложенную структуру. результат для приведенного выше примера будет

Id | number_of_employees
3  | 2
7  | 2
6  | 6
8  | 7

Answer 1

Ну вот!!

With recursive cte as (
     select id,Managerid from employee  --> Anchor Query
     union all
     select c.Id,e.ManagerId from cte c --> Recursive Member
     join employee e on (c.ManagerId=e.Id)) --> Termination Condition
select ManagerId,count(Id) as Number_of_Employees
from cte group by ManagerId

Демо

Обновить

Хорошо, позвольте мне попытаться объяснить.

Сначала нам нужно создать таблицу, в которой перечислены сотрудники под менеджером и менеджер этого менеджера до верхнего уровня (все комбинации). Должно получиться что-то вроде внизу справа? Назовем это результирующей таблицей

-------------------------
|   Id  |   ManagerId   |
-------------------------
|   1   |       3       |--
|   2   |       3       |  |
|   3   |       6       |  |
|   4   |       7       |  |->From your table
|   5   |       7       |  |
|   6   |       8       |  |
|   7   |       6       |-- 
|   2   |       6       |--
|   1   |       6       |  |
|   7   |       8       |  |
|   3   |       8       |  |
|   5   |       6       |  |-> Nested structure which you meant in the question
|   4   |       6       |  |
|   4   |       8       |  |
|   5   |       8       |  |
|   1   |       8       |  |
|   2   |       8       |--
-------------------------

Как только мы достигнем приведенной выше таблицы, это будет прямой запрос, чтобы получить count с помощью group byManagerID. Итак, как мы собираемся этого добиться.

1) Мы можем получить прямых сотрудников, используя

select Id,ManagerId from employee -- let's call this as QueryE1

2) Теперь давайте присоединимся к одной таблице, чтобы получить менеджеров первого уровня со своими сотрудниками.

select e1.Id,e2.ManagerId from employee e1 join employee e2 on e1.managerid = e2.id     
--QueryE2
-------------------------
|   Id  |   ManagerId   |
-------------------------
|   1   |       6       |
|   2   |       6       |
|   3   |       8       |
|   7   |       8       |
|   4   |       6       |
|   5   |       6       |
-------------------------

3) Затем мы должны рассматривать приведенную выше таблицу как справочную таблицу(QueryE2) и выяснять менеджеров второго уровня с их сотрудниками, присоединяясь к таблице сотрудников. Здесь, поскольку 8 является менеджером 6, все докладчики 6 также являются докладчиками 8.

SELECT e3.id,e4.managerid 
FROM   (SELECT e1.id,e2.managerid 
        FROM employee e1 JOIN employee e2 
                 ON e1.managerid = e2.id) e3 
JOIN employee e4 
ON e3.managerid = e4.id -- QueryE3

-------------------------
|   Id  |   ManagerId   |
-------------------------
|   1   |       8       |
|   2   |       8       |
|   4   |       8       |
|   5   |       8       |
-------------------------

4) Мы должны повторять вышеуказанные шаги до тех пор, пока менеджеры не исчезнут. Теперь мы знаем, что для 8 нет менеджера. Но давайте посмотрим, что говорит запрос. Теперь мы должны рассматривать последнюю таблицу (выше) как справочную.

SELECT e5.id,e6.managerid 
FROM   (SELECT e3.id,e4.managerid 
        FROM   (SELECT e1.id,e2.managerid 
                FROM   employee e1 
                       JOIN employee e2 
                         ON e1.managerid = e2.id) e3 
        JOIN employee e4 
        ON e3.managerid = e4.id) e5 
JOIN employee e6 
ON e5.managerid = e6.id  --QueryE4-- returns 0 rows as well

Наконец, если мы объединим (объединение) все значения от всех этих запросов мы получим требуемую результирующую таблицу. Все это делается нашимиRECURSIVE CTEв одном запросе. ВотQueryE1это анкерный запрос.QueryE2,QueryE3 & QueryE4являются рекурсивными членами, и они создаются нашим CTE, пока мы не получим 0 строк. После создания результирующей таблицы мы можем использовать ее для настройки наших нужд. Здесь мы делаемGroup by чтобы получить countиз ManagerID. Не уверен, что это избавит вас от недоумения, но, по крайней мере, надеюсь, что вы уловили идею:)