求部门工资最高的员工

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