网站国内服务器租用,企业网站建设流程的第一步工作是,在线购物网站设计,网站图片延时加载讲给一张表#xff0c;表字段分别为 id 、姓名、部分、经理id#xff0c;可能存在张三既是下属也是经理 现在找出下属起码有5名员工的经理 CREATE TABLE Employee (id INT,name VARCHAR(255),department VARCHAR(255),managerId INT
);INSERT INTO Employee (id, name, depar… 讲给一张表表字段分别为 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 cnt5;#方法二
######更新 使用 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 Employeegroup by ManagerIdhaving 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
; 在线sql编辑器
