表:Employees
+----------------+---------+ | Column Name | Type | +----------------+---------+ | employee_id | int | | employee_name | varchar | | manager_id | int | | salary | int | | department | varchar | +----------------+----------+ employee_id 是这张表的唯一主键。 每一行包含关于一名员工的信息,包括他们的 ID,姓名,他们经理的 ID,薪水和部门。 顶级经理(CEO)的 manager_id 是空的。
编写一个解决方案来分析组织层级并回答下列问题:
1
,CEO 的直接下属员工层级为 2
,以此类推)。返回结果表以 层级 升序 排序,然后以预算 降序 排序,最后以 employee_name 升序 排序。
结果格式如下所示。
示例:
输入:
Employees 表:
+-------------+---------------+------------+--------+-------------+ | employee_id | employee_name | manager_id | salary | department | +-------------+---------------+------------+--------+-------------+ | 1 | Alice | null | 12000 | Executive | | 2 | Bob | 1 | 10000 | Sales | | 3 | Charlie | 1 | 10000 | Engineering | | 4 | David | 2 | 7500 | Sales | | 5 | Eva | 2 | 7500 | Sales | | 6 | Frank | 3 | 9000 | Engineering | | 7 | Grace | 3 | 8500 | Engineering | | 8 | Hank | 4 | 6000 | Sales | | 9 | Ivy | 6 | 7000 | Engineering | | 10 | Judy | 6 | 7000 | Engineering | +-------------+---------------+------------+--------+-------------+
输出:
+-------------+---------------+-------+-----------+--------+ | employee_id | employee_name | level | team_size | budget | +-------------+---------------+-------+-----------+--------+ | 1 | Alice | 1 | 9 | 84500 | | 3 | Charlie | 2 | 4 | 41500 | | 2 | Bob | 2 | 3 | 31000 | | 6 | Frank | 3 | 2 | 23000 | | 4 | David | 3 | 1 | 13500 | | 7 | Grace | 3 | 0 | 8500 | | 5 | Eva | 3 | 0 | 7500 | | 9 | Ivy | 4 | 0 | 7000 | | 10 | Judy | 4 | 0 | 7000 | | 8 | Hank | 4 | 0 | 6000 | +-------------+---------------+-------+-----------+--------+
解释:
注意: