17 至少有五名直接下属的经理

[astak16]

题目

查找至少有 5 名直接下属的经理

create table employee (
	id int,
	name varchar(255),
	department varchar(255),
	managerId int
);

insert into employee 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);

SQL：方法一

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

解析

1. 按照 managerId 分组，使用 having 筛选出大于等于 5 名下属的经理 id

select managerId from employee group by managerId having count(managerId) >= 5

2. 使用 in 查出经理名字

SQL：方法二

select name from employee, (
	select managerId from employee group by managerId having count(managerId) >= 5
) as temp where employee.id = temp.managerId;

解析

1. 按照 managerId 分组，使用 having 筛选出大于等于 5 名下属的经理 id，作为临时表

select managerId from employee group by managerId having count(managerId) >= 5

2. 连查两张表，筛选条件是 employee.id = temp.managerId