查询每个部门工资最高的员工 sql

在线运行sql语句

CREATE TABLE dept (
    dno INT PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(50) NOT NULL,
    dlocal VARCHAR(100)
);
CREATE TABLE employee (
    eno INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(50) NOT NULL,
    egender CHAR(2),
    deptno INT NOT NULL,
    ejob VARCHAR(50) NOT NULL,
    esalary INT NOT NULL,
    hiredate DATE NOT NULL
);
ALTER TABLE employee AUTO_INCREMENT = 1001;
INSERT INTO dept (dname, dlocal) VALUES ('accounting', '上海');
INSERT INTO dept (dname, dlocal) VALUES ('research', '北京');
INSERT INTO dept (dname, dlocal) VALUES ('sales', '深圳');
INSERT INTO dept (dname, dlocal) VALUES ('operations', '杭州');
INSERT INTO employee (ename, egender, deptno, ejob, esalary, hiredate) VALUES
('smith', 'm', 20, 'clerk', 800, '2015-11-12'),
('allen', 'f', 30, 'salesman', 1600, '2003-05-12'),
('ward', 'm', 30, 'salesman', 1250, '2003-05-12'),
('jones', 'm', 20, 'manager', 2975, '1998-05-18'),
('martin', 'm', 30, 'salesman', 1250, '2001-06-12'),
('blake', 'f', 30, 'manager', 2850, '1997-02-15'),
('clark', 'm', 10, 'manager', 2450, '2002-09-12'),
('scott', 'm', 20, 'analyst', 3000, '2003-05-12'),
('king', 'f', 10, 'president', 5000, '1995-01-01'),
('turner', 'f', 30, 'salesman', 1500, '1997-10-12'),
('adams', 'm', 20, 'clerk', 1100, '1999-07-05'),
('james', 'f', 30, 'clerk', 950, '2008-06-15');

建表
employee结构

查询每个部门工资最高的员工

1. 使用子查询

SELECT *
FROM employee e
WHERE e.esalary = (
  SELECT MAX(esalary)
  FROM employee
  WHERE deptno = e.deptno
);

2. group by 写法

SELECT e.*
FROM employee e
INNER JOIN (
    SELECT deptno, MAX(esalary) AS max_salary
    FROM employee
    GROUP BY deptno
) max_salaries ON e.deptno = max_salaries.deptno AND e.esalary = max_salaries.max_salary;

3. 窗口函数

SELECT eno, ename, egender, deptno, ejob, esalary, hiredate
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY esalary DESC) as rank
    FROM employee
) ranked
WHERE ranked.rank = 1;