SQL实战练习题(6)-部门成员数超过4个的部门经理-leetcode sql 570

目录

• 问题描述
• 数据SQL
• 答案

问题描述

数据SQL

CREATE TABLE `Employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `departmentId` int DEFAULT NULL,
  `ManagerId` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO Employee (name,departmentId,ManagerId) VALUES
	 ('joe',1,4),
	 ('henry',2,10),
	 ('sam',2,10),
	 ('max',1,NULL),
	 ('john',1,4),
	 ('jobs',2,10),
	 ('white',2,10),
	 ('smith',1,4),
	 ('adam',3,4),
	 ('jack',2,NULL),
	 ('bon',3,4),
	 ('pony',4,10);

答案

-- 注意:Mysql 8以上才有with语句
create procedure mypro(n int)
begin
	SELECT e.ManagerId
	FROM Employee e 
	WHERE e.ManagerId is not null
	group by e.ManagerId HAVING count(1) > n;
end
;

call mypro(3);