【SQL】下属员工数量

目录

题目

分析

代码

---

题目

表：Employees

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| reports_to  | int      |
| age         | int      |
+-------------+----------+
employee_id 是这个表中具有不同值的列。
该表包含员工以及需要听取他们汇报的上级经理的 ID 的信息。 有些员工不需要向任何人汇报（reports_to 为空）。

对于此问题，我们将至少有一个其他员工需要向他汇报的员工，视为一个经理。

编写一个解决方案来返回需要听取汇报的所有经理的 ID、名称、直接向该经理汇报的员工人数，以及这些员工的平均年龄，其中该平均年龄需要四舍五入到最接近的整数。

返回的结果集需要按照 employee_id 进行排序。

结果的格式如下：

示例 1:

输入：
Employees 表：
+-------------+---------+------------+-----+
| employee_id | name    | reports_to | age |
+-------------+---------+------------+-----+
| 9           | Hercy   | null       | 43  |
| 6           | Alice   | 9          | 41  |
| 4           | Bob     | 9          | 36  |
| 2           | Winston | null       | 37  |
+-------------+---------+------------+-----+
输出：
+-------------+-------+---------------+-------------+
| employee_id | name  | reports_count | average_age |
+-------------+-------+---------------+-------------+
| 9           | Hercy | 2             | 39          |
+-------------+-------+---------------+-------------+
解释：
Hercy 有两个需要向他汇报的员工, 他们是 Alice and Bob. 他们的平均年龄是 (41+36)/2 = 38.5, 四舍五入的结果是 39.

示例 2:

输入： 
Employees 表：
+-------------+---------+------------+-----+ 
| employee_id | name    | reports_to | age |
|-------------|---------|------------|-----|
| 1           | Michael | null       | 45  |
| 2           | Alice   | 1          | 38  |
| 3           | Bob     | 1          | 42  |
| 4           | Charlie | 2          | 34  |
| 5           | David   | 2          | 40  |
| 6           | Eve     | 3          | 37  |
| 7           | Frank   | null       | 50  |
| 8           | Grace   | null       | 48  |
+-------------+---------+------------+-----+ 
输出： 
+-------------+---------+---------------+-------------+
| employee_id | name    | reports_count | average_age |
| ----------- | ------- | ------------- | ----------- |
| 1           | Michael | 2             | 40          |
| 2           | Alice   | 2             | 37          |
| 3           | Bob     | 1             | 37          |
+-------------+---------+---------------+-------------+

分析

将至少有一个其他员工需要向他汇报的员工，视为一个经理。

意思为Employees表格中有的id是员工，有的id是经理，员工需要向一个经理汇报，经理不需要。

一个表中提供了员工和经理的情况，考虑分为两表进行连

经理至少有一位下属员工，自连接可以直接过滤出符合要求的经理。

from Employees a join Employees b on a.employee_id = b.reports_to

如果用外连接，表中会包含null，需要通过where语句再次过滤

返回需要听取汇报的所有经理的 ID、名称、直接向该经理汇报的员工人数

按照经理id进行分组，group by a.employee_id

通过count获取人数，select a.employee_id, a.name,count(distinct b.employee_id) reports_count,

这些员工的平均年龄，其中该平均年龄需要四舍五入到最接近的整数

通过avg计算平均年龄，通过round进行取整，round(avg(b.age),0) average_age

返回的结果集需要按照 employee_id 进行排序

通过order by升序排列，order by a.employee_id

代码

select 
    a.employee_id,
    a.name,
    count(distinct b.employee_id) reports_count,
    round(avg(b.age),0) average_age
from Employees a join Employees b
on a.employee_id = b.reports_to
group by a.employee_id
order by a.employee_id