学会这个sql题，直接从小白变精通

说明：这个sql题来自力扣570，是一道经典，复杂但内容简单的题，非常适合新手入门练习，一定要认真看完哦~

1.lleco之570

1.1说明http://101.201.28.47:8050/problems/managers-with-at-least-5-direct-reports/submissions/549697177/

1.2分析

输入
id	name	department	managerid
101	john	A	<null>		输出
102	dan	A	101		name
103	james	A	101		john
104	amy	A	101
105	anne	A	101
106	ron	B	101
分析：输出为至少有五个下属的领导
先用自链接找到有下属的ren
id	name	department	managerid	id	name	department	managerid
101	john	A	<null>	102	dan	A	101
101	john	A	<null>	103	james	A	101
101	john	A	<null>	104	amy	A	101
101	john	A	<null>	105	anne	A	101
101	john	A	<null>	106	ron	B	101
分析:再按照领导的id进行分组，但此处只有一个领导，故不展示
分析：count 领导所带领的员工，输出>=5的领导name
		name
		john

1.3准备工作

Create table If Not Exists Employee (id int, name varchar(255), department varchar(255), managerId int) Truncate table Employee insert into Employee (id, name, department, managerId) values ('101', 'John', 'A', 'None') insert into Employee (id, name, department, managerId) values ('102', 'Dan', 'A', '101') insert into Employee (id, name, department, managerId) values ('103', 'James', 'A', '101') insert into Employee (id, name, department, managerId) values ('104', 'Amy', 'A', '101') insert into Employee (id, name, department, managerId) values ('105', 'Anne', 'A', '101') insert into Employee (id, name, department, managerId) values ('106', 'Ron', 'B', '101')

1.4实现

with t2 as( with t1 as( select e1.id id,e1.name name,e2.name name1 from Employee e1,Employee e2 where e1.id=e2.managerId ) select count(id) sum_id,name from t1 group by id,name ) select name from t2 where sum_id>=5;

总结

这道题先用自连接，找出所有有下属的领导，生成的表命名为t1，再从t1统计出领导的下属的人数，命名为t2，最后从t2中搜索出下属总人数至少5人，也就是大于等于五的领导的姓名。