1、求部门工资最高的员工,要求以列表形式给出查询结果,列字段名分别为:department,employee,salary
①建表语句
#部门表:department
CREATE TABLE department(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
NAME VARCHAR(10)
)
#员工表:employee
CREATE TABLE employee(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
NAME VARCHAR(10),
salary DECIMAL(10,2),
department_id INT DEFAULT 0
)
②初始化数据脚本
INSERT INTO department VALUES(1,'IT部'),(2,'财务部'),(3,'销售部');
INSERT INTO employee VALUES
(1,'jack',7000,1),
(2,'tom',8000,1),
(3,'joe',8000,1),
(4,'dell',9000,2),
(5,'ken', 10000,2),
(6,'tim',6000,3),
(7,'steven',7000,3);
③查询脚本
脚本a:
SELECT
d.name AS department,
e.name AS employee,
e.salary AS salary
FROM
employee e
INNER JOIN department d
ON e.department_id = d.id
WHERE (e.department_id, e.salary) IN
(SELECT
department_id,
MAX(salary)
FROM
employee
GROUP BY department_id);
脚本b:
SELECT
d.name AS department,
e.name AS employee,
c.max_salary AS salary
FROM
employee AS e
INNER JOIN
(SELECT
department_id,
MAX(salary) AS max_salary
FROM
employee
GROUP BY department_id) AS c
ON e.department_id = c.department_id
AND e.salary = c.max_salary
INNER JOIN department AS d
ON e.department_id = d.id;
脚本c:
SELECT
d.name AS department,
e.name AS employee,
c.max_salary AS salary
FROM
employee AS e,
department AS d,
(SELECT
department_id,
MAX(salary) AS max_salary
FROM
employee
GROUP BY department_id) AS c
WHERE e.department_id = c.department_id
AND e.salary = c.max_salary
AND e.department_id = d.id ;
④执行结果
推荐使用脚本a