一、题目
二、代码
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
三、运行结果