1731. 每位经理的下属员工数量
思路分析
对于此问题,我们将至少有一个其他员工需要向他汇报的员工,视为一个经理。
编写一个解决方案来返回需要听取汇报的所有经理的 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 |
+-------------+---------+---------------+-------------+
分析与解答过程
这道题其实就是考我们怎么根据表中的数据字段,提取符合条件的记录,稍微有点复杂。
我来重新描述一下:
其实就是要求我们找到所有经理的 employee_id,name以及在记录中需要向他们汇报的人数reports_count 和这些人的平均年龄average_age。
这里的关键点就是在表中找到身份为经理的数据,我们看示例可以知道,reports_to字段内的值就为经理的employee_id。这样我们可以将表用reports_to字段连接起来,找到所有reports_to的人的名字。
使用下面的语句连接
SELECT
E1.name,E1.employee_id,E1.reports_to,E1.age,
E2.employee_id,E2.name
FROM Employees E1
LEFT JOIN Employees E2 ON E1.reports_to=E2.employee_id
;
我使用的数据集如下:
执行sql语句后,我们得到了这样的一组数据:
这个结果中,当E2.employ_id不为NULL时,可以判定这个employ_id对应的人为经理。
我们添加这个条件到sql语句内得到结果如下:
最后的结果要求我们筛选出所有经理的employee_id和name以及对应向他们回报的下属,以及下属的平均年龄。
这里我们可以按照E2.employee_id来分组,然后使用 COUNT(E1.reports_to) AS reports_count, ROUND(AVG(E1.age)) AS average_age来获取汇报量和平均年龄。
最后我们按照E2.employee_id排序即可。
完整的解答sql语句如下:
解答
SELECT
E2.employee_id,E2.name,
COUNT(E1.reports_to) AS reports_count, ROUND(AVG(E1.age)) AS average_age
FROM Employees E1
LEFT JOIN Employees E2 ON E1.reports_to=E2.employee_id
WHERE E2.employee_id IS NOT NULL
GROUP BY E2.employee_id
ORDER BY E2.employee_id
;
总结
这道题的考点主要是以下几个:
- 表自连接,筛选记录【匹配身份】
- 按照NOT NULL 过滤数据
- GROUP BY
- ORDER BY
- COUNT,ROUND,AVG聚合函数的使用