[SQL智慧航行者] - 至少有五名直接下属的经理

话不多说, 先看数据表信息.

数据表信息:

employee表包含所有员工及其上级的信息。每位员工都有一个id，并且还有一个对应主管的id(managerid).

+------+----------+-----------+----------+
|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       |
+------+----------+-----------+----------+

话不多说, 再看需求~

需求:

写一条SQL语句找出有5个下属的主管。对于上面的表，结果应输出：

+-------+
| Name  |
+-------+
| John  |
+-------+

话不多说, 进行拆解~

拆解:

我理解的是直接进行多表连接, 然后对 id 进行聚合, 使用count函数进行输出结果.

select t1.*, t2.*  
from employee as t1 
left join employee as t2 
on t1.id = t2.managerid
;

我们先把不是领导的员工删除,

select t1.*, t2.*  
from employee as t1 
left join employee as t2 
on t1.id = t2.managerid
where t2.id is not null 
;

这个时候, 左表的内容就是领导的信息了, 我们对左表的id进行聚合计数, 将符合条件的进行保留:

select 
    t.name 
from (
    select t1.id as managerid, t1.name, t2.id   
    from employee as t1 
    left join employee as t2 
    on t1.id = t2.managerid
    where t2.id is not null 
) as t 
group by t.managerid
having count(t.id) >= 5
;

最后给大家介绍一下我这边的操作步骤, 想要自己测试的话, 可以参考:

CREATE TABLE employee (
    Id INT,
    Name VARCHAR(50),
    Department VARCHAR(50),
    ManagerId INT
);

INSERT INTO employee (Id, Name, Department, ManagerId)
VALUES (101, 'John', 'A', NULL),
       (102, 'Dan', 'A', 101),
       (103, 'James', 'A', 101),
       (104, 'Amy', 'A', 101),
       (105, 'Anne', 'A', 101),
       (106, 'Ron', 'B', 101);

结果如下: