表: Employee
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | department | varchar | | managerId | int | +-------------+---------+ id 是此表的主键(具有唯一值的列)。 该表的每一行表示雇员的名字、他们的部门和他们的经理的id。 如果managerId为空,则该员工没有经理。 没有员工会成为自己的管理者。
编写一个解决方案,找出至少有五个直接下属的经理。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入: Employee 表: +-----+-------+------------+-----------+ | id | name | department | managerId | +-----+-------+------------+-----------+ | 101 | John | A | Null | | 102 | Dan | A | 101 | | 103 | James | A | 101 | | 104 | Amy | A | 101 | | 105 | Anne | A | 101 | | 106 | Ron | B | 101 | +-----+-------+------------+-----------+ 输出: +------+ | name | +------+ | John | +------+
算法一
首先查询每个人的下属员工数。将两份 Employee 表用 join 操作连接。Manager 表代表经理,Report 表代表下属,每对 Manager.Id=Report.ManagerId 的情况代表此经理的一名下属。再根据 Manager.Id 分组,对 Report.Id 求和得到每个经理对应的下属数量。
select Manager.Name as Name, count(Report.Id) as cnt
from
Employee as Manager join Employee as Report
on Manager.Id = Report.ManagerId
group by Manager.Id
将此表命名为 ReportCount,再在其中筛选 cnt>=5
的数据项即可。最终查询为
select Name
from (
select Manager.Name as Name, count(Report.Id) as cnt
from
Employee as Manager join Employee as Report
on Manager.Id = Report.ManagerId
group by Manager.Id
) as ReportCount
where cnt >= 5
算法二
上述查询在分组后,可以直接使用 having 字句筛选下属大于 5 的经理,代码如下
select Manager.Name as Name
from
Employee as Manager join Employee as Report
on Manager.Id = Report.ManagerId
group by Manager.Id
having count(Report.Id) >= 5
算法三
上面的查询为了得到经理的名字,首先对两份 Employee 表进行了连接,但是我们其实可以先对经理进行筛选,再通过连接操作得到经理的名字。
要筛选员工数大于等于 5 的经理,直接将 Employee 表根据 ManagerId 进行分组,每组中的 Id 即为每个经理对应的每个下属,取下属数量大于等于 5 的条目。
select ManagerId as Id
from Employee
group by ManagerId
having count(Id) >= 5
将此表命名为 Manager,随后与 Employee
表做连接操作,得到每个经理的名字。
select Employee.Name as Name
from (
select ManagerId as Id
from Employee
group by ManagerId
having count(Id) >= 5
) as Manager join Employee
on Manager.Id = Employee.Id