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

原创已于 2024-01-20 18:18:22 修改 · 495 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2024-01-20 17:49:58 首次发布

综合部同时被 2 个专栏收录

130 篇文章

订阅专栏

sql

8 篇文章

订阅专栏

文章描述了如何使用SQL查询来找出在给定员工表中，那些作为经理且拥有至少5名直接下属的经理名字。方法涉及将表拆分为经理和下属表，通过JOIN操作并应用GROUPBY和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
;