2022-12-03：部门工资最高的员工。以下数据 Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。sql 语句如何写？输出结果如下： department emp

2022-12-03
北京
本文字数：726 字
阅读完需：约 2 分钟

2022-12-03：部门工资最高的员工。以下数据 Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。sql 语句如何写？输出结果如下：department employee salaryIT Jim 90000Sales Henry 80000IT Max 90000

表结构和数据如下：

DROP TABLE IF EXISTS `department`;CREATE TABLE `department` (  `id` int(11) NOT NULL,  `name` varchar(255) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `department` VALUES ('1', 'IT');INSERT INTO `department` VALUES ('2', 'Sales');
DROP TABLE IF EXISTS `employee`;CREATE TABLE `employee` (  `id` int(11) NOT NULL,  `name` varchar(255) NOT NULL,  `salary` int(11) NOT NULL,  `department_id` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `employee` VALUES ('1', 'Joe', '70000', '1');INSERT INTO `employee` VALUES ('2', 'Jim', '90000', '1');INSERT INTO `employee` VALUES ('3', 'Henry', '80000', '2');INSERT INTO `employee` VALUES ('4', 'Sam', '60000', '2');INSERT INTO `employee` VALUES ('5', 'Max', '90000', '1');

复制代码

答案 2022-12-03：

sql 语句如下：

SELECT    department.name AS 'department',    employee.name AS 'employee',    salaryFROM    employee        JOIN    department ON employee.department_id = department.idWHERE    (employee.department_id , salary) IN    (   SELECT            department_id, MAX(salary)        FROM            employee        GROUP BY department_id  )