该题目来源于力扣:
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
总结:当遇到需要对调立场的,相关联的多列数据时,尝试使用自连接可以发现不同的大陆。