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 |
+------------+----------+--------+
解答
- 创建表
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);
- 创建表
Department
CREATE TABLE Department
(id INTEGER,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id));
INSERT INTO Department VALUES(1,'IT'),
(2,'Sales');
- 查找部门最高工资的员工
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 );-- 选择的条件