MySQL-06练习：部门最高工资的员工

MySQL-06练习： 部门最高工资的员工

题目

表：Employee

+--------------+---------+
| 列名          | 类型    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id是此表的主键列。
departmentId是Department表中ID的外键。
此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。

表：Department

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id是此表的主键列。
此表的每一行都表示一个部门的ID及其名称。

编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。例如：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

解答

1. 创建表Employee

CREATE DATABASE kbase;
USE kbase;
CREATE TABLE Employee
(id INTEGER,
 name VARCHAR(100) NOT NULL,
 salary INTEGER,
 department_id INTEGER,
 PRIMARY KEY (id));

INSERT INTO Employee VALUES(1,'Joe',70000,1),
						   (2,'Henry',80000,2),
						   (3,'Sam',60000,2),
						   (4,'Max',90000,1);

2. 创建表Department

CREATE TABLE Department
(id INTEGER,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id));

INSERT INTO Department VALUES(1,'IT'),
							 (2,'Sales');

3. 查找部门最高工资的员工

SELECT Department.name AS 'Department', Employee.name AS 'Employee', Salary             
FROM Employee
		 JOIN
	 Department ON  Employee.department_id = Department.id -- 按条件连结表Employee和表Department
WHERE (Employee.department_id, Employee.salary) IN 
	  (SELECT department_id, MAX(salary)
  		FROM Employee 
		GROUP BY department_id );-- 选择的条件