编写一个解决方案来返回需要听取汇报的所有经理的 ID、名称、直接向该经理汇报的员工人数,以及这些员工的平均年龄, # 其中该平均年龄需要四舍五入到最接近的整数。 #返回的结果集需要按照 employee_id 进行排序。
题目链接:
https://leetcode.cn/problems/the-number-of-employees-which-report-to-each-employee/description/
建表插入语句:
Create table If Not Exists Employees(employee_id int, name varchar(20), reports_to int, age int)
Truncate table Employees
insert into Employees (employee_id, name, reports_to, age) values ('9', 'Hercy', 'None', '43')
insert into Employees (employee_id, name, reports_to, age) values ('6', 'Alice', '9', '41')
insert into Employees (employee_id, name, reports_to, age) values ('4', 'Bob', '9', '36')
insert into Employees (employee_id, name, reports_to, age) values ('2', 'Winston', 'None', '37')
画图分析:
代码实现:
select e1.employee_id,e1.name,
count(e2.employee_id) reports_count,
round(avg(e2.age)) average_age
from employees e1 join employees e2 on
e1.employee_id=e2.reports_to
group by e1.employee_id,e1.name order by e1.employee_id desc;