Medium之570.至少有5名直接下属的经理

分手后的思念是犯贱 2022-10-13 12:36 9阅读 0赞

Employee 表  
:包含所有员工和他们的经理。每个员工都有一个 Id，并且还有一列是经理的 Id。

<table> 
 <thead> 
  <tr> 
   <th>Id</th> 
   <th>Name</th> 
   <th>Department</th> 
   <th>ManagerId</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>101</td> 
   <td>John</td> 
   <td>A</td> 
   <td>null</td> 
  </tr> 
  <tr> 
   <td>102</td> 
   <td>Dan</td> 
   <td>A</td> 
   <td>101</td> 
  </tr> 
  <tr> 
   <td>103</td> 
   <td>James</td> 
   <td>A</td> 
   <td>101</td> 
  </tr> 
  <tr> 
   <td>104</td> 
   <td>Amy</td> 
   <td>A</td> 
   <td>101</td> 
  </tr> 
  <tr> 
   <td>105</td> 
   <td>Anne</td> 
   <td>A</td> 
   <td>101</td> 
  </tr> 
  <tr> 
   <td>106</td> 
   <td>Ron</td> 
   <td>B</td> 
   <td>101</td> 
  </tr> 
 </tbody> 
</table>

### 问题 ###

给定 Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。对于上表，您的SQL查询应该返回：

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/managers-with-at-least-5-direct-reports  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

##### 法一：自联结 #####

select b.Name
    from employee a,employee b
    where a.managerId = b.id
    group by b.id
    having count(*)>=5

##### 法二：子查询 #####

select Name
    from employee
    where id in
        (select distinct managerId
        from employee
        group by managerId
        having count(managerId)>=5)