sql570 | 至少有5名下属的经理｜ join on | group by

sql570 | 至少有5名下属的经理｜ join on | group by | having

讲给一张表，表字段分别为 id 、姓名、部分、经理id，可能存在张三既是下属也是经理
现在找出下属起码有5名员工的经理

CREATE TABLE Employee (
    id INT,
    name VARCHAR(255),
    department VARCHAR(255),
    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),
    (107, 'tt1', 'B', 102),
    (108, 'tt2', 'B', 102),
    (109, 'tt3', 'B', 102),
    (110, 'tt4', 'A', 102),
    (111, 'tt5', 'A', 103),
    (112, 'tt6', 'A', 103),
    (113, 'tt7', 'A', 103);

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
)
where cnt >= 3;

在这里插入图片描述

###################################
思路讲同一张表变成两张表，一张经理表，一张下属表，然后根据经理表的id == 员工表中经理表id 进行 join on  生成一张新表，然后在用筛选条件 找出cnt >= 5 的name

#######	参考
#######注意 方法一 开销很大
# 找出至少有五个直接下属的经理  --> 找出 经理 下面至少有5个员工
-- select name
-- from Employee
-- where id in (select managerId
--             from Employee
--             group by    managerId
--             having count(*) >= 5);
/*
john 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
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;


######方法3
select Employee.name as name
from(
    select ManagerId 
    from Employee
    group by ManagerId
    having count(ManagerId) >= 5
)as Manager join Employee
on Manager.ManagerId = Employee.Id;

以下是gpt 对group by 和 having 的解释
在这里插入图片描述

CREATE TABLE Employee (
    id INT,
    name VARCHAR(255),
    department VARCHAR(255),
    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),
    (107, 'tt1', 'B', 102),
    (108, 'tt2', 'B', 102),
    (109, 'tt3', 'B', 102),
    (110, 'tt4', 'C', 102),
    (111, 'tt5', 'C', 103),
    (112, 'tt6', 'C', 103),
    (113, 'tt7', 'D', 103);

select count(Employee.department)
from  
Employee
group by Employee.department
having count(Employee.department) >=3
;