[每日一练]利用自连接实现数量查询

该题目来源于力扣：

1731. 每位经理的下属员工数量 - 力扣（LeetCode）

题目要求：

表：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.

思路流程：

可见，我们需要将被报道的人作为emoloyee_id的形式，显示向他们报告的employee_id，并将这里的employee_id的数量化为reports_count。我们如果想将原表格中的reports_to的id转换为employee_id，可以使用自连接，使前表的emoloyee_id=后表的reports_to，我们先连接查看结果

select * average_age 
from Employees e1
join Employees e2
on e1.reports_to=e2.employee_id 

/*
| employee_id | name  | reports_to | age | employee_id | name  | reports_to | age |
| ----------- | ----- | ---------- | --- | ----------- | ----- | ---------- | --- |
| 4           | Bob   | 9          | 36  | 9           | Hercy | null       | 43  |
| 6           | Alice | 9          | 41  | 9           | Hercy | null       | 43  |
*/

由此可见，在连接的表中，原reports_to中的数据成功对调。在自连接的表中，我们可以用到前表的age列进行平均值的计算，记得用round函数实现四舍五入。其他要求的数据都在后表。

代码实现

# Write your MySQL query statement below
select e2.employee_id,e2.name,count(e2.name) reports_count,round(avg(e1.age),0) average_age 
from Employees e1
join Employees e2
on e1.reports_to=e2.employee_id 
group by e2.employee_id 
order by e2.employee_id asc

总结：当遇到需要对调立场的，相关联的多列数据时，尝试使用自连接可以发现不同的大陆。