力扣---高频SQL题---30---1731. 每位经理的下属员工数量

一、题目

二、代码

-- # Write your MySQL query statement below


select  Employees.employee_id , Employees.name ,count(distinct table2.employee_id  ) as reports_count ,round(avg(table2.age),0) as average_age from Employees
join
(
    select Employees.employee_id , Employees.name, Employees.reports_to  , Employees.age  from Employees 
    join
    (
        select distinct reports_to  from Employees
        where reports_to is not null
    ) as table1
    where Employees.reports_to  = table1.reports_to
) as table2
where Employees.employee_id  = table2.reports_to
group by Employees.employee_id

三、运行结果